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

Question

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$1:$E$1 & "*")) = 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.

Here is the process I do:

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

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.

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

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

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

=SUM(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 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.

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?

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$1:$E$1 & ""*"")) = 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$1:$E$1 & ""*"")) = 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$1:$E$1 & ""*"")) = 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$1:$E$1 & ""*"")) = 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

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-03-07T23:24:16

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:

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

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

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

    .Add Type:=xlExpression, Formula1:= _
        "=SUMPRODUCT(COUNTIF(A1,""*"" & $B$1:$E$1 & ""*"")) = 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.

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

1 Answers