VBA to set Conditional Formatting only for cells with False value

Question

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?

Shai Rado · Accepted Answer · 2017-02-20 09:22:23Z

2

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

answered Feb 20, 2017 at 9:22

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gizlmo · Accepted Answer · 2017-02-20 09:19:32Z

2

Try to match it as Text:

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

answered Feb 20, 2017 at 9:19

gizlmo

1,9221 gold badge16 silver badges14 bronze badges

Collectives™ on Stack Overflow

VBA to set Conditional Formatting only for cells with False value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related