Условное форматирование Excel vba с использованием динамического цветового ключа

Я пытаюсь создать макрос Excel, который применяет условное форматирование к целевому столбцу, используя условие ячеек в другом столбце и формат ячеек в еще одном столбце (по сути, цветовой ключ).

Цветовой ключ представляет собой одну столбчатую диаграмму с цветными ячейками, содержащими текст в каждой строке (например, синяя ячейка с «синим» в качестве текста).

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

Это сэкономит много времени, так как цветов много, и они должны точно соответствовать RGB.

Вот что у меня есть на данный момент:

Sub ColorCode()

'Applies conditional formatting to Input Chart using the Color Key

Application.ScreenUpdating = False

Dim ColorKey As Range

Set ColorKey = Worksheets(2).Range("C6:C19")

Dim kCell As Object
Dim lCell As Object
Dim mCell As Object

With Worksheets(2)
    For Each mCell In Worksheets(2).Range("Input[Duration1]")
        If mCell.Value <> "0" Then
            For Each lCell In Worksheets(2).Range("Input[Color1]")
                If lCell.Value <> "" Then
                    For Each kCell In ColorKey.Cells
                        If lCell.Value = kCell.Value Then
                            mCell.Interior.Color = kCell.Interior.Color
                            mCell.Font.Color = kCell.Font.Color
                        End If
                    Next
                End If
            Next
        End If
    Next
End With

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

Перед добавлением "application.screenupdating=false" я вижу, как цвета мерцают, пока он зацикливается, но они просто не прилипают. Когда я пытаюсь добавить "ByVal Target as Range" в свой код, мой макрос исчезает, и, честно говоря, даже несмотря на то, что я просмотрел это, я действительно не понимаю, что это означает.

Я новичок в VBA и почти уверен, что мне не хватает чего-то простого. Буду очень признателен за любую помощь с этим!

Я отмечаю ответ как ответ - вот обновленный код!

Sub getcol()

Dim rr As Range
Dim tg  As Range

    Set color_dict = CreateObject("Scripting.Dictionary"

        For Each rr In Range("colorkey")
            color_dict.Add rr.Text, rr.Interior.Color
        Next


        For Each rr In Range("input[color1]")
            rr.Offset(0, -2).Interior.Color = color_dict(rr.Text)

        Next

        End Sub

dynamic excel conditional-formatting vba color-key
person JuliaXu    schedule 01.03.2017    source источник
comment
Не уверен, что здесь уместен вложенный цикл - разве вы не хотите только раскрасить ячейку в той же строке, что и mCell?   -  person Tim Williams    schedule 01.03.2017

Ответы (1)


arrow_upward
0
arrow_downward

Не совсем понимаете правило решения, какого цвета должна быть целевая ячейка из вашего описания.

Но в любом случае вы, вероятно, захотите создать dictionary для хранения цветов по text ключу. А затем используйте этот словарь, чтобы перебрать ваш целевой диапазон и установить цвет ячейки, прочитав текст в этой целевой ячейке (??)

ниже я предполагаю, что текст для создания ключа словаря находится в столбце рядом с диапазоном color_key. Если текст, который вы хотите прочитать, чтобы создать ключ словаря, фактически находится в том же столбце, удалите offset (или установите его на 0 смещений столбца).

Я предполагаю, что color_key и target_Range являются именованными диапазонами в ваших таблицах Excel (где-то).

Sub getcol()

Dim rr As Range
Set color_dict = CreateObject("Scripting.Dictionary")

 For Each rr In Range("color_key")
    color_dict.Add rr.Offset(0, 1).Text, rr.Interior.Color
 Next

 For Each rr In Range("target_Range")
    rr.Interior.Color = color_dict(rr.Text)
 Next

End Sub

Код CELL.Interior.Color возвращает целочисленный код, представляющий цвет заливки ячейки, как вы упомянули, вам нужен точно такой же цвет.

Словарь dict работает, считывая пары (ключ, значение) с использованием синтаксиса dict.Add key, value. И возвращает значение, когда ему передается соответствующий ключ: dict(key)=value.

person LaplaceKis    schedule 01.03.2017
comment
Правило состоит в том, что если текст в целевой ячейке совпадает с текстом в ячейке с цветовым ключом, целевая ячейка должна иметь внутренний цвет ячейки с цветовым ключом. Да! Я думаю, что словарь, основанный на вашем описании, вполне подойдет; однако я получаю ошибку времени выполнения 457, ключ уже связан с элементом этой коллекции. Размышления о том, почему это может быть? - person JuliaXu; 01.03.2017
comment
Обновление - я не читал ваши заметки раньше. Поскольку мой ключевой текст находится в том же столбце, я удалил смещение из color_dict.add rr.offset (0,1) .text, rr.interior.color и добавил смещение в rr.interior.color = color_dict (rr.text) поэтому цвет будет применен к другому столбцу, и это прекрасно сработает. Большое спасибо!! - person JuliaXu; 01.03.2017
comment
При использовании словаря VBA, если вы попытаетесь добавить элемент с ключом, который уже связан с элементом в словаре, VBA выдаст ошибку. Но при тщательном отлове ошибок это обычный шаблон для фильтрации только уникальных элементов (он намного быстрее и гибче, чем другие методы). По сути, вы хотите resume next on error. - person LaplaceKis; 02.03.2017