Excel VBA - conditional formatting of cells in a loop

Question

I have a number of figures in cells in range C1:E3. I want to apply a conditional formatting of the cells in each row, based on the value provided in column 1. So, for example for range C1:E1 I want to fill with red all the cells with a value greater than value in cell A1, for range C2:E2 - with a value greater than A2 etc. I've tried to write a loop, but I don't know how to properly refer to the values that determine formatting - in the code below it's the part "Formula1:="=A&row". How to do it right?

Sub color()

For Row = 1 To 3

    Range(Cells(Row, 3), Cells(Row, 5)).Select
    Application.CutCopyMode = False
    With Selection
         .FormatConditions.Delete

         .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=A&row"
        .FormatConditions(1).Interior.color = RGB(255, 0, 0)

    End With

Next Row
End Sub

@Chris, your loop is fine. You can do it another way but if something works as is why not declare victory. If you had a huge number of iterations the .Select could slow you down. I would avoid using "Row" as a variable name since it is a keyword already, as a matter of habit. But again, if it is working in your example, ok! — klausnrooster

klausnrooster klausnrooster · Accepted Answer · 2018-09-04T21:04:23

The $A1 thing is not intuitive unless you've used Excel / Conditional Formatting a lot, but this also works:

Sub color()

    Application.CutCopyMode = False
    With Range(Cells(1, 3), Cells(3, 5))
      .FormatConditions.Delete

      .FormatConditions.add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=$A1"
     .FormatConditions(1).Interior.color = RGB(255, 0, 0)

    End With
End Sub

Excel VBA - conditional formatting of cells in a loop

1 Answers