vba 在宏的帮助下将多个条件格式应用于同一列

Applying multiple conditional formatting to the same column with help of macro

提问by Acidon

I have a formula to conditionally format text in a column based on the value from another cell (not in same column):

我有一个公式可以根据另一个单元格（不在同一列中）的值有条件地格式化列中的文本：

=SUM(COUNTIF(A1,"*" & $B:$E & "*")) = 1

More detailed description can be found here.

更详细的描述可以在这里找到。

I ran into problem while trying to apply this formula with the help of a macro I've recorded with "Record a macro" in Excel 2010.

我在 Excel 2010 中使用“记录宏”录制的宏的帮助下尝试应用此公式时遇到问题。

Here is the process I do:

这是我做的过程：

1) First I select the entire column (by clicking on column letter on top).

1）首先我选择整个列（通过单击顶部的列字母）。

2) Then I go to "Conditional Formatting > New rule > Use a formula to determine which cells to format", enter my formula and choose a fill color for my formatting.

2）然后我转到“条件格式>新规则>使用公式来确定要设置格式的单元格”，输入我的公式并为我的格式选择填充颜色。

3) I then repeat the step 2 for 3 other conditional formattings with different formulas as well as different fill colors:

3) 然后我对 3 个其他具有不同公式和不同填充颜色的条件格式重复步骤 2：

=SUM(COUNTIF(A1,"*" & $B:$E & "*")) = 2

=SUM(COUNTIF(A1,"*" & $B:$E & "*")) = 3

=SUM(COUNTIF(A1,"*" & $B:$E & "*")) = 4

So here is the problem: while recording the macro I get the desired formatting, i.e. all the cells in A column are color coded depending on how many values from cells B1, C1, D1 and E1 match it's contents.

所以问题是：在录制宏时，我得到了所需的格式，即 A 列中的所有单元格都进行了颜色编码，具体取决于单元格 B1、C1、D1 和 E1 中与其内容匹配的值的数量。

However when I try to run previously recorded macro on unformated column it doesn't work as intended, only changing color of some cells and only in one color.

但是，当我尝试在未格式化的列上运行以前录制的宏时，它无法按预期工作，仅更改某些单元格的颜色且仅更改一种颜色。

So the question is: How do I apply multiple conditional formatting to the same column with help of macro in Excel 2010?

所以问题是：如何在 Excel 2010 中的宏的帮助下将多个条件格式应用于同一列？

Below is a full macro code for your references:

以下是供您参考的完整宏代码：

Sub Macro6()
'
' Macro6 Macro
'

'
    Columns("A:A").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=SUM(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=SUM(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=SUM(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 3"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=SUM(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 4"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

回答by Dmitry Pavliv

Changing your formula from =SUM(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1(used with array entry) to =SUMPRODUCT(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1(used without array entry) fixes the issue. Actually thouse formulas give you the same result. So, this code works:

将公式从=SUM(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1（与数组条目一起=SUMPRODUCT(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1使用）更改为（在没有数组条目的情况下使用）可解决此问题。实际上thouse公式给你相同的结果。所以，这段代码有效：

With Columns("A:A").FormatConditions
    .Add Type:=xlExpression, Formula1:= _
        "=SUMPRODUCT(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 1"
    With .Item(.Count).Interior
        .Color = 49407
    End With

    .Add Type:=xlExpression, Formula1:= _
        "=SUMPRODUCT(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 2"
    With .Item(.Count).Interior
        .Color = 5296274
    End With

    .Add Type:=xlExpression, Formula1:= _
        "=SUMPRODUCT(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 3"
    With .Item(.Count).Interior
        .Color = 15773696
    End With

    .Add Type:=xlExpression, Formula1:= _
        "=SUMPRODUCT(COUNTIF(A1,""*"" & $B:$E & ""*"")) = 4"
    With .Item(.Count).Interior
        .Color = 255
    End With
End With

Not sure why, but when using your code, I should explicitly goto FormatConditions on the sheet and press "Apply" button for each rule to make it work. The most lakely issue is that thouse formulas are array formuas.

不知道为什么，但是在使用您的代码时，我应该明确转到工作表上的 FormatConditions 并为每个规则按“应用”按钮以使其工作。最棘手的问题是这些公式是数组公式。