VBA Excel：将条件格式应用于BLANK单元格 [英] VBA Excel: Apply conditional formatting to BLANK cells

问题描述

我正在尝试编写一个子过程，它将一些条件格式化应用于Excel中的单元格范围。
我有点卡住了，所以我用了宏录音机。我不知道为什么它应用下面的公式，当我手动运行代码失败。

• 我想做什么将条件格式应用于范围中的空白单元格。


• 我想使单元格颜色为灰色


• 范围是一个表格和表格被称为Table1。


• 我需要在sub中执行此操作，因为该表将动态刷新。

下面是不起作用的记录宏，而是将格式应用于错误的单元格。
任何帮助纠正它将不胜感激

谢谢

  Sub MacroTest（）
 
范围（Table1）。选择
'以下公式是错误的，但我无法弄清楚它应该是
 Selection.FormatConditions。添加类型：= xlExpression，Formula1：== LEN（TRIM（D15））= 0
 With Selection.FormatConditions（1）.Interior 
 .PatternColorIndex = xlAutomatic 
 .ThemeColor = xlThemeColorAccent2 
 .TintAndShade = 0.399945066682943 
结束
 Selection.FormatConditions（1）.StopIfTrue = False 
 
 End Sub 
  / pre> 
 
解决方案
尝试这个（试用和测试）
 
 
 
更改
  Selection.FormatConditions.Add类型：= xlExpression，Formula1：== LEN（TRIM D15））= 0
  
 to 
  Selection.FormatConditions.Add类型：= xlExpression，Formula1：= _ 
= LEN（ TRIM（C&范围（Table1）。行& ））= 0
  
所以你的代码可以写成
  Sub Sample（）
 With ThisWorkbook.Sheets（Sheet1）。Range（Table1）
 .FormatConditions.Add类型： = xlExpression，Formula1：= _ 
= LEN（TRIM（C& .Row&））= 0
 .FormatConditions（.FormatConditions.Count）.SetFirstPriority 
 With .FormatConditions（1）.Interior 
 .PatternColorIndex = xlAutomatic 
 .ThemeColor = xlThemeColorDark2 
 .TintAndShade = -9.99481185338908E-02 
结束
结束
 End Sub 
  
 
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.


What I want to do is apply conditional formatting to the blank cells in the range.
I want to make the cell color grey
The range is a table and the table is called 'Table1'.
I need to do this in a sub because the table refreshes dynamically.


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

解决方案
Try this (Tried and tested)

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


                        
这篇关于VBA Excel：将条件格式应用于BLANK单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！