Excel VBA - How to ignore blank cells in conditional formatting

Question

I am trying to highlight cells that have a date less than today's date. However, when applying conditional format, "ALL" blank cell's are highlighted. I am aware of specifying the range (I2:I200), but the report is run on a daily basis which can consist of 1 to 200+. This is why I need the entire column formatted.

Sheets("Sheet1").Columns("I:I").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=today()"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False

score 2 · Answer 1 · answered Dec 19 '16 at 21:04

Just an additional point:

The solution does not work if any of the dates are produced by formulas.

If you want to highlight date constants and date formulas, you need to go back to the original selection (or do the same thing by assigning a range):

Sheets("Sheet1").Columns("I:I").Select

... and replace the conditional format with something like this:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=if(isblank(i1),false,i1<=today())"

(I'm not expert at conditional formatting, so I don't really understand why it works when you refer to the first cell in the range, but it did when I tested it.)

BruceWayne · Accepted Answer · 2016-12-19T20:27:05.180

You can use a SpecialCells() type. The first line is all I changed.

Sheets("Sheet1").Columns("I:I").SpecialCells(xlCellTypeConstants).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=today()"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False

However, you should also avoid using .Select. I'll leave how to do that as an exercise for the reader.

Excel VBA - How to ignore blank cells in conditional formatting

2 Answers2