Conditional rormatting rules not applying consistently in Excel 2010

[mstrpeez]

I've come across some strange behaviour in Excel 2010 when using VBA to apply conditional formatting rules to multiple ranges. What appears to be happening is that after corrently creating 3 conditional formatting rules for 20 different ranges any additional conditional formatting rules created will assign their formatting properties to the incorrect conditional formatting rule.

I tried this in both Excel 2007 and Excel 2010 and it only happens in Excel 2010. (on both Windows XP and Windows 7)


An example :
- Create 3 conditional formatting rules for 50 different cells (i know that i can create 3 rules and apply them to a single range but for the purposes of an easy example i am creating 3 separate rules for each cell)
- rule 1 : "The Green Rule" - make font color green
- rule 2 : "The Yellow Rule" - make font color yellow
- rule 3 : "The Red Rule" - make font color red

What Happens :
The conditional formatting rules are created correctly for the first 20 cells but after that the green font color is applied to the Yellow rule, the Yellow font color is applied to the red rule and the Green rule has no formatting.

Please help, i cannot understand what is causing this behaviour.

Here is the code i used to generate this scenario. You can just paste it into a module in a blank workbook, run it, then take a look at the conditional formatting in cells A20 versus cell A21.

Sub CFTest()
  Dim lRow As Long
  For lRow = 1 To 50
    With ActiveSheet.Cells(lRow, 1)
      .FormatConditions.Add Type:=xlExpression, Formula1:="=""Red"""
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Font.Color = -16777024 ' a red color
      .FormatConditions(1).StopIfTrue = True
    
      .FormatConditions.Add Type:=xlExpression, Formula1:="=""Yellow"""
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Font.Color = -16711681  ' a yellow color
      .FormatConditions(1).StopIfTrue = True
    
      .FormatConditions.Add Type:=xlExpression, Formula1:="=""Green"""
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Font.Color = -11480942 ' a green color
      .FormatConditions(1).StopIfTrue = True
    End With
  Next lRow
End Sub

Any help would be much appreciated.

 

[mstrpeez]

I was curious about the same thing and tried it earlier and the formatting error still occurs at and after the 21st range.