Excel VBA - 如何在条件格式中忽略空白单元格

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.

我试图突出显示日期少于今天日期的单元格。但是,在应用条件格式时,会突出显示“ALL”空白单元格。我知道指定范围(I2:I200),但报告每天运行,可以包含1到200+。这就是我需要格式化整个列的原因。

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

2 个解决方案

#1

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

您可以使用SpecialCells()类型。第一行是我改变了。

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.

但是,您还应该避免使用.Select。我会留下如何做到这一点作为读者的练习。

#2

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

(我不是条件格式的专家,所以当你引用范围中的第一个单元格时,我并不真正理解它为什么会起作用,但是当我测试它时它就会这样做。)

#1