I really like the enhanced conditional formatting in Excel 2007. Today I found out that the FormatConditions property in VBA behaves slightly differently in Excel 2007 than it used to in 2003.

In particular, if the format condition is based on a cell value, then the Formula1 property of the FormatContition object in Excel 2003 returns a numeric value. i.e. if the conditional format is 'Cell Value Is' greater than 100, then the Formula1 property will eturn the number 100 in Excel 2003. In Excel 2007 this will return a string - "=100".

This presents a problem when writing code that works for both Excel 2003 and 2007. The short sample below shows how to test the Formula1 property so that it works in both versions of Excel.

Sub TestFormatCondition()
Dim rng As Range
Dim valCond1 As Double

Set rng = Worksheets(1).Range("A1")

With rng.FormatConditions(1)
  ' check if formula condition is numeric (Excel 2007 uses string)
  If IsNumeric(.Formula1) Then
    valCond1 = .Formula1
  ElseIf IsNumeric(Mid(.Formula1, 2, 1)) Then
    valCond1 = CDbl(Right(.Formula1, Len(.Formula1) - 1))
  Else
    Debug.Print "Condition is Formula"
  End If
End With

Debug.Print valCond1

End Sub