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.

 

[RoryA]

Do you have SP1 applied? Your code runs fine for me.

 

[mstrpeez]

Re: Conditional formatting rules not applying consistently in Excel 2010

I don't think so... Excel versino is listed as 14.0.4760.1000, there is no mention of SP1 andwhere that i can see.

Unfortunately i am in a business environment where i do not have the ability to control Office updates.

Rorya, did you examine the conditional formatting of cells A20 and A21 when you tried it? The code runs fine for me as well (in that it doesn't generate any errors) but the conditional formatting doesn't apply as expected after the 20th range.

 

[RoryA]

Re: Conditional formatting rules not applying consistently in Excel 2010

Yes, I did check it and all cells have the same formatting.
My version is 14.0.6023.1000

 

[mstrpeez]

Re: Conditional formatting rules not applying consistently in Excel 2010

I guess it's good that it's not an issue for those that are able to get timely Office updates... I, however, appear to be boned.

 

[RoryA]

Re: Conditional formatting rules not applying consistently in Excel 2010

Any better if you use:

Sub CFTest2()
   Dim lRow              As Long
   Dim FC                As FormatCondition
   For lRow = 1 To 50
      With ActiveSheet.Cells(lRow, 1)
         Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:="=""Red""")
         With FC
            .SetFirstPriority
            .Font.Color = -16777024   ' a red color
            .StopIfTrue = True
         End With

         Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:="=""Yellow""")
         With FC
            .SetFirstPriority
            .Font.Color = -16711681  ' a yellow color
            .StopIfTrue = True
         End With
         Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:="=""Green""")
         With FC
            .SetFirstPriority
            .Font.Color = -11480942   ' a green color
            .StopIfTrue = True
         End With
      End With
   Next lRow
End Sub

If not, I think you need to shout at your IT people (assuming you can't ignore them and do it yourself)

 

[mstrpeez]

Thanks for the suggestion about assigning the formatcondition.add to a variable and setting the properties off the variable, however, the issue persists even using this method.

I work at a big company and the wheels turn pretty slowly when it comes to software and OS upgrades. Looks like i'll need to be judicious about my conditional formatting until we get SP1.

Thanks for your help rorya.

 

[RoryA]

Any change if you move the SetFirstPriority commands after setting up the formats? If not, I'm afraid you are stuck - there are a few CF related bugs in 2010 but if you can't update, there's not much you can do really.

 

[mstrpeez]

I tried putting SetFirstPriority to immediately after the condition formatting, and i tried removing SetFirstPriority entirely... everything produces the same mismatching of formatting to rules after the 20th range.

Very frustrating...

 

[RoryA]

Does it work with separate loops of 20 at a time? (idle curiosity, so only check if you're bored. )