VBA Excel：将条件格式应用于空白单元格

VBA Excel: Apply conditional formatting to BLANK cells

提问by Eddie

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.

我正在尝试编写一个子过程，该过程将一些条件格式应用于 Excel 中的一系列单元格。我有点卡住了所以我使用了宏记录器。但是，我无法弄清楚为什么它应用下面的公式，并且当我手动运行代码时它会失败。

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

• 我想要做的是将条件格式应用于范围内的空白单元格。
• 我想让单元格颜色变灰
• 该范围是一个表，该表称为“Table1”。
• 我需要在 sub 中执行此操作，因为表会动态刷新。

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

回答by Siddharth Rout

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

回答by Jerome Montino

Here's my take, even if Sid already has a great answer. I recreated a table with name testand positioned it at A1. I used a minor edit of your code and it works fine for me.

这是我的看法，即使 Sid 已经有了很好的答案。我重新创建了一个带有名称的表test并将其定位在A1. 我对您的代码进行了小幅编辑，对我来说效果很好。

Sub Test()
Dim v As Range
Set v = Range("test")
v.ClearFormats
v.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A1))=0"
With v.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399945066682943
End With
v.FormatConditions(1).StopIfTrue = False
End Sub

Just as a note, though, the usage of A2inside the formula can produce inflexible results, especially compared to what Sid used in his code above.

不过，请注意，A2在公式中使用 的会产生不灵活的结果，尤其是与 Sid 在上面的代码中使用的相比。

Hope it helps (or at least gives some insight)!

希望它有所帮助（或至少提供一些见解）！

SECOND TAKE:

第二个：

This has been bothering me since the other day so I'll give it another shot. Apparently, based on this Microsoft Support nugget, there seems to be issues with CF as it is. Two workarounds exist: either by using absolute reference or by selecting the cell first before applying CF.

从前几天开始这一直困扰着我，所以我再试一次。显然，基于此Microsoft Support nugget，CF 似乎存在问题。存在两种解决方法：使用绝对引用或在应用 CF 之前先选择单元格。

I played around a bit and got wrong results a lot of times with absolute reference. However, one simple approach works. We select the first cell of Table1and give it CF, and then we use the simplest approach in the book: format painter! We also replaced .ClearFormatswith .FormatConditions.Delete.

我玩了一会儿，在绝对参考下多次得到错误的结果。但是，一种简单的方法有效。我们选择的第一个单元格Table1并给它CF，然后我们使用书中最简单的方法：格式画家！我们也.ClearFormats用.FormatConditions.Delete.

Here's a variation of your code with the aforementioned approach:

这是使用上述方法的代码的变体：

Sub Test()
Dim Table1 As Range: Set Table1 = ThisWorkbook.Sheets("Sheet1").Range("Table1")
    Start = Timer()
    Application.ScreenUpdating = False
    Table1.FormatConditions.Delete
    With Table1.Cells(2, 1)
    'With Range("B7")
        .Select
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(B7))=0"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = 0.399945066682943
        End With
        .FormatConditions(1).StopIfTrue = False
        .Copy
    End With
    Table1.PasteSpecial xlPasteFormats 'or the next one
    'Range("B7:AO99").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Debug.Print Timer() - Start
End Sub

Here's a preview of results.

这是结果的预览。

Execution times (in seconds) are:

执行时间（以秒为单位）是：

• 4.296875E-02
• 4.492188E-02
• 5.273438E-02
• 5.859375E-02
• 0.0625

• 4.296875E-02
• 4.492188E-02
• 5.273438E-02
• 5.859375E-02
• 0.0625

These are much faster than a previous attempt of mine where I looped through all the cells and added CF to each.

这些比我之前的尝试要快得多，我之前尝试遍历所有单元格并向每个单元格添加 CF。

Hope this helps you!

希望这对你有帮助！

回答by Harry

After some searching I found an option that works without using the function LEN and needing to specify the range using the xlBlanksCondition. I do not why the macro recorder comes up with the LEN solution if it also could have used the xlBlanksCondition solution.

经过一番搜索，我找到了一个无需使用函数 LEN 即可工作的选项，并且需要使用 xlBlanksCondition 指定范围。如果宏记录器也可以使用 xlBlanksCondition 解决方案，我不知道为什么它会提出 LEN 解决方案。

Source: MSDN Microsoft

来源：MSDN 微软

I first select a range and then I apply this code:

我首先选择一个范围，然后应用此代码：

With Selection.FormatConditions.Add(Type:=xlBlanksCondition)
    .StopIfTrue = False
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.Color = RGB(226, 80, 80)
    .Interior.ThemeColor = xlThemeColorAccent2
    .Interior.TintAndShade = 0.39
    .Font.Color = vbBlack
    .Font.TintAndShade = 0
    .Borders.LineStyle = xlContinuous
    .Borders.TintAndShade = 0
    .Borders.Weight = xlThin
    .Borders.Color = RGB(255, 0, 0)
    .StopIfTrue = False
End With