Применение множественного условного форматирования к одному столбцу с помощью макроса

У меня есть формула для условного форматирования текста в столбце на основе значения из другой ячейки (не в том же столбце):

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

Более подробное описание можно найти здесь.

У меня возникла проблема при попытке применить эту формулу с помощью макроса, записанного с помощью функции «Записать макрос» в Excel 2010.

Вот как я делаю:

1) Сначала я выбираю весь столбец (щелкая букву столбца вверху).

2) Затем я перехожу к «Условное форматирование»> «Новое правило»> «Использовать формулу для определения ячеек для форматирования», ввожу формулу и выбираю цвет заливки для моего форматирования.

3) Затем я повторяю шаг 2 для трех других условных форматов с разными формулами и разными цветами заливки:

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

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

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

Итак, вот проблема: во время записи макроса я получаю желаемое форматирование, то есть все ячейки в столбце A имеют цветовую кодировку в зависимости от того, сколько значений из ячеек B1, C1, D1 и E1 соответствуют его содержимому.

Однако, когда я пытаюсь запустить ранее записанный макрос в неформатированном столбце, он не работает должным образом, меняет только цвет некоторых ячеек и только один цвет.

Возникает вопрос: как применить несколько условных форматов к одному и тому же столбцу с помощью макроса в Excel 2010?

Ниже приведен полный код макроса для справок:

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

excel conditional-formatting excel-formula vba
person Acidon    schedule 07.03.2014    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Изменение формулы с =SUM(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1 (используется с записью массива) на =SUMPRODUCT(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1 (используется без записи массива) устраняет проблему. На самом деле эти формулы дают тот же результат. Итак, этот код работает:

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

Не знаю, почему, но при использовании вашего кода я должен явно перейти к FormatConditions на листе и нажать кнопку «Применить» для каждого правила, чтобы оно заработало. Самая большая проблема заключается в том, что эти формулы являются формулами массивов.

person Dmitry Pavliv    schedule 07.03.2014
comment
Ах, сработало как шарм, я уже начал тянуть за волосы :) Мне тоже нравится, как вы сделали код короче, теперь выглядит очень аккуратно, большое спасибо! - person Acidon; 08.03.2014