VBA - FormatConditions inconsistent behavior

Question

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

Answer 1

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

 

Try:

Public Sub m()

    Dim workRange As Range

    Set workRange = _

        ThisWorkbook.Worksheets("Sheet1").Range("A1:A25")

    With workRange

        With .FormatConditions

            .Delete

            .Add xlCellValue, xlBetween, 20, 100

            .Add xlCellValue, xlNotBetween, 20, 100

        End With

        .FormatConditions(1).Font.ColorIndex = 10

        .FormatConditions(2).Font.ColorIndex = 3

    End With

    Set workRange = Nothing

End Sub

Or:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim workRange As Range

    Set workRange = Me.Range("A1:A25")

    If Not Intersect(Target, workRange) Is Nothing Then

        With Target

            With .FormatConditions

                .Delete

                .Add xlCellValue, xlBetween, 20, 100

                .Add xlCellValue, xlNotBetween, 20, 100

            End With

            .FormatConditions(1).Font.ColorIndex = 10

            .FormatConditions(2).Font.ColorIndex = 3

        End With

    End If

    Set workRange = Nothing

End Sub

Or:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim workRange As Range

    Set workRange = Me.Range("A1:A25")

    On Error Resume Next

    If Not Intersect(Target, workRange) Is Nothing Then

        With Target

            If .Value >= 20 And .Value <= 200 Then

                .Font.ColorIndex = 10

            Else

                .Font.ColorIndex = 3

            End If

        End With

    End If

    Set workRange = Nothing

End Sub

---

Mauro Gamberini - Microsoft© MVP(Excel)

http://www.maurogsc.eu/