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

 

[JoeMo]

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

I think you want to set the two conditions for the entire range rather than one cell at a time. Try this:

Sub Macro1()
Dim workRange As Range
Dim iRow As Long
iRow = Range("A" & Rows.Count).End(xlUp)
Set workRange = Range("A1", "A" & iRow)
With workRange
    .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
End Sub

 

[AlphaFrog]

Try something like this...

Sub Macro1()

    With Range("A1:A25")
    
        .Font.ColorIndex = 3  'Default Font color
        
        .FormatConditions.Delete
        .FormatConditions.Add xlCellValue, xlBetween, 20, 100
        .FormatConditions(1).Font.ColorIndex = 10
        
    End With

End Sub

 

[codemomma]

Thank you both for some great answers to my question.

First, to JoeMo, while this would work for the sample code I posted, in reality, I have different ranges set on the different cells. One cell could have a range of 20 to 100 while another could have one from 50 to 1000 and so forth. So applying one set of conditions to the whole range would not work for my situation.

To AlphaFrog, this solution will give me a great workaround. All of the cells in the column I am working with have a green font regardless of whether they have a conditional rule added or not. So just applying the one rule to turn the font red if the cell value is out of range is really great.


Thank you both so much for your time.

 

[alphajoe]

Hello Folks,

I come exactly across the same kind of erratic behaviour in my Excel 2010.

Trying to color-code cells with 3 states :
- default is black text
- value greater than something : text red
- value less then something else : text green

Unfortunately I cannot rely on AlphaFrog's "default color" trick, since I need 3 states, hence 2 FormatConditions and a default color. As codemomma described it, Excel 2003 works great but Excel 2010 doesn't.

Any clue or workaround would be HIGHLY appreciated.

 

[alphajoe]

Hello Folks,

I come exactly across the same kind of erratic behaviour in my Excel 2010.

Trying to color-code cells with 3 states :
- default is black text
- value greater than something : text red
- value less then something else : text green

Unfortunately I cannot rely on AlphaFrog's "default color" trick, since I need 3 states, hence 2 FormatConditions and a default color. As codemomma described it, Excel 2003 works great but Excel 2010 doesn't.

I tried different approaches like FormatConditions.Add followed by a FormatCondition.Modify of the newly created condition. VBA shows that the 2 conditions share all properties given at creation/modification stage (Type, Operator, Formula1) and all cell/text formating properties like Font.ColorIndex or Interior.ColorIndex, even after a call to Modify. The only differing property in VBE is the Priority.

However, the GUI and the resulting format do exhibit something completely different : the conditions actually do have differing creation/modification properties (Type, Operator, Formula1) but only the first condition actually get some formating properties set (twice, so only the properties intended to the second condition are set, but to the first condition).

Any clue or workaround would be HIGHLY appreciated.

 

[Norie]

You should easily be able to apply 3 formats for a range, as long as you get the formulas right - in particular the cell referencing.

What are your conditions?

What range do you want to apply them conditional formatting to?

One thing you should probably be careful of in 2010 is the increased no of formats that can be applied.

That's generally a good thing but if you are coding things, and the coding isn't quite working, you could end up just adding new formats 'on top' of the existing ones.

So you could end up with a whole load of formats, most of which on their own would work OK, but with all the others maybe not...

The best thing to do is clear all formatting before applying any new formatting.

That's quite simple:

With Range("A1:A10").FormatConditions
       .Delete
       .Add...
       ' rest of code for conditions and formats.
End With

Are you already doing that?

 

[mikerickson]

Try putting both of the two .FormatConditions.Add lines before the .FormatConditions(1) line.

 

[alphajoe]

Tank you Norie and mikerikson for you advices but nothing really helps.

Here is a snippet that you can throw at you Excel if you want to see to erratic behaviour of Excel.

Sub TestFormatConditions()
    Dim FmtTarget As Range
    Set FmtTarget = ActiveSheet.Range("S23")
 
    ' Basic test
    Debug.Print "Basic Test"
    Debug.Print "----------"
    FmtTarget.FormatConditions.Delete
    With FmtTarget.FormatConditions.Add(xlCellValue, xlGreater, "=M23")
        .Font.ColorIndex = 3    ' Red
    End With
    With FmtTarget.FormatConditions.Add(xlCellValue, xlLess, "=M23")
        .Font.ColorIndex = 50   ' Green
    End With
    With FmtTarget
        Debug.Print "Comparing the two FormatConditions:"
        Debug.Print "Type            : ", .FormatConditions(1).Type, .FormatConditions(2).Type
        Debug.Print "Operator        : ", .FormatConditions(1).Operator, .FormatConditions(2).Operator
        Debug.Print "Formula1        : ", .FormatConditions(1).Formula1, .FormatConditions(2).Formula1
        Debug.Print "Font.ColorIndex : ", .FormatConditions(1).Font.ColorIndex, .FormatConditions(2).Font.ColorIndex
    End With
    Debug.Print
 
    ' Mikerickson test
    Debug.Print "Alternate Test"
    Debug.Print "--------------"
    FmtTarget.FormatConditions.Delete
    Call FmtTarget.FormatConditions.Add(xlCellValue, xlGreater, "=M23")
    Call FmtTarget.FormatConditions.Add(xlCellValue, xlLess, "=M23")
    With FmtTarget.FormatConditions(1)
        .Font.ColorIndex = 3    ' Red
    End With
    With FmtTarget.FormatConditions(2)
        .Font.ColorIndex = 50   ' Green
    End With
    With FmtTarget
        Debug.Print "Comparing the two FormatConditions:"
        Debug.Print "Type            : ", .FormatConditions(1).Type, .FormatConditions(2).Type
        Debug.Print "Operator        : ", .FormatConditions(1).Operator, .FormatConditions(2).Operator
        Debug.Print "Formula1        : ", .FormatConditions(1).Formula1, .FormatConditions(2).Formula1
        Debug.Print "Font.ColorIndex : ", .FormatConditions(1).Font.ColorIndex, .FormatConditions(2).Font.ColorIndex
    End With
    Debug.Print
End Sub

The result I get in Execution Window is as follows :

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

As you can see, no one of the two approches do work.

Even worse is the fact that editing rules through Excel GUI display correct condition but erroneous formatting, which definitely do not match the Debug results.

 

[AlphaFrog]

I think your With statements are incorrect.

I ran your code and it didn't produce an error, but I did get some funky results. The Debug Print formulas didn't match the actual formulas in the CF dialog.

I don't think you should have...

With FmtTarget.FormatConditions.Add(xlCellValue, xlGreater, "=M23")

And then consider the following a property of .Add

.Font.ColorIndex = 3    ' Red

Maybe try something like this (I don't have 2010 to test this on)...

Sub AlphaFrog_Test()

    Dim FmtTarget As Range
    Set FmtTarget = ActiveSheet.Range("S23")

    With FmtTarget
    
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=M23"
        .FormatConditions(1).Font.ColorIndex = 3
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=M23"
        .FormatConditions(2).Font.ColorIndex = 50

        ' AlphaFrog test
        Debug.Print "Alpha Test"
        Debug.Print "--------------"
        Debug.Print "Comparing the two FormatConditions:"
        Debug.Print "Type            : ", .FormatConditions(1).Type, .FormatConditions(2).Type
        Debug.Print "Operator        : ", .FormatConditions(1).Operator, .FormatConditions(2).Operator
        Debug.Print "Formula1        : ", .FormatConditions(1).Formula1, .FormatConditions(2).Formula1
        Debug.Print "Font.ColorIndex : ", .FormatConditions(1).Font.ColorIndex, .FormatConditions(2).Font.ColorIndex
        
    End With
    
    Debug.Print
    
End Sub