1004 Error in VBA Conditional Formatting Code €“ Fails in 2007
 

I have a VBA sub which works in Excel 2003 but fails in Excel 2007.

When the worksheet is recalculated, such as a pivot table update, the sub is
called and the error is generated. The sub changes the font and interior
color based upon the value in two specified cells. As written the failure
occurs on the following line:

Selection.FormatConditions(2).Font.ColorIndex = 2

Interestingly I can comment out the following two lines and line above works
without issue.

Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 3

The sub follows:

Sub AddFormatConditions()
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Percent Funded'", _
xlDataAndLabel
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$G$5"
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$J$5"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions(2).Font.ColorIndex = 2
Selection.FormatConditions(2).Interior.ColorIndex = 41

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Percent Funded'", _
xlLabelOnly
Selection.FormatConditions.Delete
Range("F14").Select
Range("a1").Select
End Sub