CODEWITHSUNDEEP
excelvba
Rey Taino
Rey Taino

Reputation: 179

Excel VBA - How to ignore blank cells in conditional formatting

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

Upvotes: 0

Views: 1856

Answers (2)

vknowles
vknowles

Reputation: 784

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.)

Upvotes: 2

BruceWayne
BruceWayne

Reputation: 23285

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.

Upvotes: 1

Related Questions