VBA - FormatConditions strange bug

[codemomma]

Hello, I have some code that loops thru cells and adds two conditional formatting conditions per cell. Basically, if the value of the cell is within the range, I want the font to be green. If the value is outside the range, the number should be red. The following code works just fine sometimes, but other times the formatting condition rules get messed up.

When I step thru the code, I can see what is happening but do not understand why. For the cells where the format conditioning gets messed up, the first rule is created just fine with green for the format color. Then, the second rule gets created OK but on the line of code where I try to set the format color to red, it applies it to the first rule instead of the second rule! Again, this only happens sometimes and not all the time in my loop.

FYI - this code worked just fine in Excel 2003 and I am now trying to get it to work in 2010.

On a worksheet, I entered some numbers between 0 and 200 in cells A1 - A25

Sub Macro1()
Dim workRange As Range
Dim iRow as Integer
Set workRange = Range("A1")
For iRow = 0 To 24
With workRange.Offset(iRow, 0)
.FormatConditions.Delete
.FormatConditions.Add xlCellValue, xlBetween, 20, 100
.FormatConditions(1).Font.ColorIndex = 10
.FormatConditions.Add xlCellValue, xlNotBetween, 20, 100
.FormatConditions(2).Font.ColorIndex = 3
End With
Next
End Sub

I am wondering if anyone can help me with this
Thank you

 

[alphajoe]

Thank's again for your effort AlphaFrog. Unfortunately, I don't think that the WITH clauses are in cause. Your test code is functionally identical to the Alternate Test in mine.

OK, so I went back to the simplest possible test bed: new workbook, one module with my test code... and guess what? It does work as expected!

Results in Immediate Window :

Basic Test
----------
Comparing the two FormatConditions:
Type            :            1             1 
Operator        :            5             6 
Formula1        :           =M23          =M23
Font.ColorIndex :            3             50
 
Alternate Test
--------------
Comparing the two FormatConditions:
Type            :            1             1 
Operator        :            5             6 
Formula1        :           =M23          =M23
Font.ColorIndex :            3             50

This is right:
First of all, both tests do produce the same expected results. Now, look at the properties, Type and Formula1 are identical as requested at creation time. On the contrary, Operator and Font.ColorIndex differ, which is exactly what was asked for.

On the Excel 2010 GUI side, the conditional formatting rules are shown as created in VBA. And the expected behavior is exhibited by cell S23 (turns red when value is greater than M23, turns green when less than M23 and remains black when equal to M23).

So what's actually going on?

The sheet in which I intend to apply these formatting rules does contain quite a lot of conditional formatting (about 100+ cells with different combinations of rules). The whole contents of the sheet is reconstructed dynamically in VBA as a result of an analysis process initiated by the user and the amount of data depends on a selection made by the user, so I really cannot rely on Excel GUI to preconfigure my sheet.

It really looks like Excel is just messing something up when too many conditional formatting rules are defined.

So I changed the creation sequence, adding the formatting rules of cells at line 23 (a line with totals whose position differ in function of the analyzed data) BEFORE all others... and it does work!

Well... most of the time.

As I said, the content of the sheet does vary in function of selection parameters given by the user. So do consequently the positions (the rows and lines) of the cells to be conditionally formatted. I noticed so far that Excel fails in managing the rules about 1/25 of the time with this new creation sequence.

Did I fail to mention that Excel 2003 does the job right all the time?

Excel 2010 certainly offers some interesting new functions but it also introduces some painful drawbacks that are tricky to circumvent. I already got strange behavior when trying to size and position chart legends and text frames in Excel 2007.

I am going to investigate the point further, and hopefully find a creation sequence that succeeds every time. I will post my findings if I get something new.

Thank you for your support.

 

[Norie]

Forgot to add, try using R1C1 notation for the formulas rather than A1.

I noticed similar behaviour in early versions, including the formatting working sometimes.

Didn't like to rely on that so I tried a few other things.

One of those was switching to R1C1 notation.

It seemed to work every time, and the conditions/formulas I was applying were quite complex.

Maybe worth a try.

 

[alphajoe]

Nice advice Norie, thank you.

I certainely would like to use R1C1 formulas but my firsts attempts showed that Excel wants local (language-dependent) ones.

Since my users may have Office in different languages, I have to convert the formulas on-the-fly. Unfortunately, I don’t know how to convert standard R1C1 formula to local R1C1.

Maybe Application.ConvertFormula can help but so far I failed getting what I want.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Norie]

What's local R1C1?

I honestly haven't encountered that before.

Mind you I think VBA might be locality 'blind' if you know what I mean.

For example when you are creating using VBA to create formulas in a version of Excel that uses ; instead of , for a formula delimiter, you actually use the latter.

ie the , not the ;

 

[drsarao]

Had similar problem.
MikeRickson's solution (post #8) of adding ALL the conditions first before setting the formats worked like a charm.
Didn't try Nori's R1C1 suggestion though. Pressed for time.
Thanks