Excel：使用 VBA 将条件格式应用于单个列中的空白单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20325120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Using VBA to apply conditional formatting to blank cells in individula columns
提问by user3056778
I have applied the duplicate conditional format to columns AB, AD, AR, AT, BH & BJ across a worksheet with around 1500 rows, however it is also highlighting the blanks.
我已将重复的条件格式应用于大约 1500 行的工作表中的 AB、AD、AR、AT、BH 和 BJ 列，但它也突出显示了空白。
I have tried to create an additional conditional format of changing any of the blanks to a white cell colour, as I can't find a way to remove the conditional format from the blank cells.
我试图创建一个额外的条件格式，将任何空白更改为白色单元格颜色，因为我找不到从空白单元格中删除条件格式的方法。
The only way I have found to highlight the cells white at the moment is to use the conditional formatting again, which works perfectly when I record the macro but not when I replay this as it turns either the whole column white, or leaves blank cells as red.
目前我发现将单元格高亮显示为白色的唯一方法是再次使用条件格式，这在我录制宏时效果很好，但在我重播时不起作用，因为它将整列变成白色，或者留下空白单元格作为红色的。
This is the vba code of the additional conditional format:
这是附加条件格式的vba代码：
Sub Macro3()
Range("I:I,AB:AB,AD:AD,AR:AR,AT:AT,BH:BH,BJ:BJ").Select
Range("BJ1").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(BJ1))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Help is greatly appreciated as I've spent far too long trying to find a work around!
非常感谢帮助，因为我花了太长时间试图找到解决方法！
回答by lowak
Try add another filter with formula isblank=truefor the same cells.
尝试isblank=true为相同的单元格添加另一个带有公式的过滤器。
回答by brettdj
Try this
尝试这个
Sub Macro3()
Range("I:I,AB:AB,AD:AD,AR:AR,AT:AT,BH:BH,BJ:BJ").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(I1))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
