VBA Excel: Apply conditional formatting to BLANK cells

Question

I'm trying to write a sub procedure which applies some conditional formatting to a range of cells in Excel. I'm getting a bit stuck so I used the Macro recorder. I can't however figure out why it's applying the formula below and when I run the code manually it fails.

• What I want to do is apply conditional formatting to the blank cells in the range.
• I want to make the cell color grey
• The range is a table and the table is called 'Table1'.
• I need to do this in a sub because the table refreshes dynamically.

Below is the recorded macro which doesn't work and instead applies formatting to the wrong cells. Any help correcting it would be appreciated

Thanks

Sub MacroTest()

    Range("Table1").Select
    'The below formula is wrong but I can't figure out what it should be
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(D15))=0"
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

Solution 1

Change

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(D15))=0"

to

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(C" & Range("Table1").Row & "))=0"

So your code can be written as

Sub Sample()
    With ThisWorkbook.Sheets("Sheet1").Range("Table1")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(C" & .Row & "))=0"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -9.99481185338908E-02
        End With
    End With
End Sub

Solution 2

Here's my take, even if Sid already has a great answer. I recreated a table with name test and positioned it at A1. I used a minor edit of your code and it works fine for me.

Sub Test()
Dim v As Range
Set v = Range("test")
v.ClearFormats
v.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A1))=0"
With v.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399945066682943
End With
v.FormatConditions(1).StopIfTrue = False
End Sub

Just as a note, though, the usage of A2 inside the formula can produce inflexible results, especially compared to what Sid used in his code above.

Hope it helps (or at least gives some insight)!

SECOND TAKE:

This has been bothering me since the other day so I'll give it another shot. Apparently, based on this Microsoft Support nugget, there seems to be issues with CF as it is. Two workarounds exist: either by using absolute reference or by selecting the cell first before applying CF.

I played around a bit and got wrong results a lot of times with absolute reference. However, one simple approach works. We select the first cell of Table1 and give it CF, and then we use the simplest approach in the book: format painter! We also replaced .ClearFormats with .FormatConditions.Delete.

Here's a variation of your code with the aforementioned approach:

Sub Test()
Dim Table1 As Range: Set Table1 = ThisWorkbook.Sheets("Sheet1").Range("Table1")
    Start = Timer()
    Application.ScreenUpdating = False
    Table1.FormatConditions.Delete
    With Table1.Cells(2, 1)
    'With Range("B7")
        .Select
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(B7))=0"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = 0.399945066682943
        End With
        .FormatConditions(1).StopIfTrue = False
        .Copy
    End With
    Table1.PasteSpecial xlPasteFormats 'or the next one
    'Range("B7:AO99").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Debug.Print Timer() - Start
End Sub

Here's a preview of results.

Execution times (in seconds) are:

• 4.296875E-02
• 4.492188E-02
• 5.273438E-02
• 5.859375E-02
• 0.0625

These are much faster than a previous attempt of mine where I looped through all the cells and added CF to each.

Hope this helps you!

Solution 3

After some searching I found an option that works without using the function LEN and needing to specify the range using the xlBlanksCondition. I do not why the macro recorder comes up with the LEN solution if it also could have used the xlBlanksCondition solution.

Source: MSDN Microsoft

I first select a range and then I apply this code:

With Selection.FormatConditions.Add(Type:=xlBlanksCondition)
    .StopIfTrue = False
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.Color = RGB(226, 80, 80)
    .Interior.ThemeColor = xlThemeColorAccent2
    .Interior.TintAndShade = 0.39
    .Font.Color = vbBlack
    .Font.TintAndShade = 0
    .Borders.LineStyle = xlContinuous
    .Borders.TintAndShade = 0
    .Borders.Weight = xlThin
    .Borders.Color = RGB(255, 0, 0)
    .StopIfTrue = False
End With

Comments

[WitchGod]

I always learn something new from you, Sid. Just a bit curious, though. Should .ClearFormats be first to clean any existing CF, or is it alright when done like this? :)

[Siddharth Rout]

A slight change to your code :) If you are placing the table in A1 then the formula has to be "=LEN(TRIM(A1))=0" instead of "=LEN(TRIM(A2))=0" :) else you will get incorrect result like OP and hence .Row like I used :)

[WitchGod]

Sorry about that. I meant A1. Changed the above. Been staring at SQL exports for the last 8 hours and so I'm still stuck at this header thing. :D

[Siddharth Rout]

+ 1 For .ClearFormats :)

[WitchGod]

@SiddharthRout: While I am honored to accept the upvote, I am now questioning .ClearFormats. If I have any formatting in the cells, like mayhaps Currency, they will be stripped as well. Now, I can re-apply them after applying cond. formatting, but it feels like cheating in some way. :D

[Eddie]

Thanks for the replies. I'm trying out both suggestions but getting inconsistent results. Maybe I have an issue with my table. The table starts in cell B6 (the first header) and goes onto AO:99. When I try the above code I still get inconsistent result. I have cleared all other formatting conditions. I understand that the formula above is evaluating the contents of a cell but it even colors a cell which has data in it.

[Siddharth Rout]

@Eddie: What does Debug.print ThisWorkbook.Sheets("Sheet1").Range("Table1").Address give you? Replace "Sheet1" with the actual sheet name

[WitchGod]

Apparently, CF is one of the few times where .Select is necessary. And where .PasteSpecial xlPasteFormats shine. @Eddie: See my new code below. It's a simple workaround, but it works flawlessly.

[Eddie]

apologies for the delay in my response. Problem was with my worksheet and not with your code which works great. Thanks for the help. All good.

[Eddie]

@BK201, it does help. Thank you.

[RCaetano]

This should be the aceepted answer since it does not rely on the Excel country language (while writting the formula) but rather on an internal Excel variable (xlBlanksCondition)