VBA COnditional Formatting Not Working

[Ark68]

Hoping someone is able to correct me ...
With a Google search, I found some direction on how to apply conditional formatting to cells with VBA. With some adaptation, I came up with the following code:

          With sh
                statval = .Range("O4")
                Set rngcf = .Range("H13:Q" & rwpdaed)
                .Unprotect
                For Each cell In rngcf
                    cell.FormatConditions.Delete
                    Debug.Print cell.Address
                    Set condition1 = cell.FormatConditions.Add(xlCellValue, xlEqual, "=" & Left(statval, 3))
                    Set condition2 = cell.FormatConditions.Add(xlCellValue, xlNotEqual, "=" & Left(statval, 3))
                    With condition1
                        .Font.Color = vbWhite
                    End With
                    With condition2
                        '.Font.colour = RGB(166, 166, 166)
                        .Interior.Color = RGB(166, 166, 166)
                    End With
                Next cell
           End With

The idea is to check each cell in the range (rngcf). If the value in that cell equals the value represented by the first three letters of 'statval', the cell's font colour turns to white. If the cell value isn't equal to the first three letters of the value 'statval', the cell background is shaded grey.

This isn't working. I'm not sure if my conditions are wrong, of the format application is wrong.

 

[rlv01]

You don't need to loop through the cells. You can set the format condition for the entire range.

    With sh
        Set rngcf = .Range("H13:Q" & rwpdaed)
        With rngcf.FormatConditions
            .Delete
            Set condition1 = .Add(Type:=xlExpression, Formula1:="=Left(H13,3)=$O$4")
            condition1.Font.Color = vbWhite
            Set condition2 = .Add(Type:=xlExpression, Formula1:="=Left(H13,3)<>$O$4")
            condition2.Interior.Color = RGB(166, 166, 166)
        End With
    End With

 

[Ark68]

Thank you so much. This is a much better solution than what I was dealing with.