VBA to set Conditional Formatting only for cells with False value

0
votes

i'm using VBA to apply conditional formatting to a sheet, setting the background color and front color if the content is "FALSE":

Sub Formatting()

    Sheets("File").Cells.FormatConditions.Delete

    With Range("N2:N2000").FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=$N2=FALSE")
        .Interior.Color = RGB(255, 239, 239)
        .Font.Color = RGB(97, 0, 0)
    End With
End Sub

However, Blank cells also get red.

How to only apply the conditional formatting to cells with "False" value?

2
vbaexcel

2 Answers

2
votes

First, make sure to fully qualify your Range("N2:N2000") with Sheets("File").

Second, if you want to apply the formatting only to the word "FALSE", without extra characters inside the cell use TextOperator:=xlEqual, otherwise use TextOperator:=xlContains.

Code

Option Explicit

Sub Formatting()

    With Sheets("File")
        .Cells.FormatConditions.Delete

        With .Range("N2:N2000").FormatConditions.Add( _
            Type:=xlTextString, String:="FALSE", TextOperator:=xlEqual)
            .Interior.Color = RGB(255, 239, 239)
            .Font.Color = RGB(97, 0, 0)
        End With
    End With

End Sub
2
votes

Try to match it as Text:

Type:=xlTextString, String:="FALSE", TextOperator:=xlContains