CODEWITHSUNDEEP
vbaexcelconditional-formatting
Eddie
Eddie

Reputation: 621

VBA Excel: Apply conditional formatting to BLANK cells

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.

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

Upvotes: 2

Views: 20433

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

Upvotes: 4

Harry
Harry

Reputation: 51

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

Upvotes: 5

WGS
WGS

Reputation: 14179

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!

Upvotes: 4

Related Questions