VBA Excel: Apply conditional formatting to BLANK cells

2
votes

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
3
vbaexcelconditional-formatting
By "wrong cells", do you mean that it doesn't target zero-length values or that it's randomly placing the formatting everywhere in Table 1? – NullDev
Correct. It doesn't target zero-length values / blank cells and is placing the formatting in cells which have date values. I'm guessing the formula is completely wrong and needs to be re-written. I've looked at using the IsEmpty and IsBlank functions in the formula but not bee able to figure it out correctly. – Eddie
@Eddie I know this post is old, but for future visitors, an elegant solution is to simply use Selection.FormatConditions.Add Type:=xlBlanksCondition with no need for any formula1! – Wolfie

3 Answers

4
votes

Try this (Tried and tested)

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
4
votes

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.

Sample Table1

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!

4
votes

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