Excel vba conditional formatting using dynamic color key

Question

I'm trying to create an Excel macro that applies conditional formatting to a target column using the condition of cells in another column and the format of cells in yet another column (essentially a color key).

The color key is a single column chart with colored cells containing text in each row (e.g. blue cell with "blue" as the text).

The goal is to be able to change the fill colors or text in the color key and have the target cells automatically change to the new colors or condition without having to hardcode the new RGB through Excel's conditional formatting rules manager.

This would save a lot of time as there are a lot of colors, and they must be the exact RGB match.

Here's what I have so far:

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

This loops through each of the cells in the columns and actually colors them in. The issue is all of the cells are colored to the condition of the last cell, so all of the colors are the same rather than each cell being formatted for its own condition.

Before adding "application.screenupdating=false", I can see the colors flickering while it's looping, but they just won't stick. When I try to add "ByVal Target as Range" to my code, my macro disappears, and to be honest, even though I've looked this up I don't really understand what this means.

I'm new to VBA, and am pretty sure I'm missing something simple. I'd really appreciate any help with this!

I'm marking this as answered - Here is the updated code!

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

Not sure a nested loop is appropriate here - don't you only want to color the cell on the same row as mCell ? — Tim Williams

LaplaceKis LaplaceKis · Accepted Answer · 2017-03-01T01:20:03

Not quite understanding the rule to decide what color the target cell should be from your description.

But in any case, you probably want to create a dictionary to store colors against a text key. And then use this dictionary to loop over your target range and set the color of the cell by reading the text in that target cell (??)

in the below, I assume the text to create the dictionary key is in the column next to range color_key. If the text you wish to read in to create the dictionary key is actually in the same column then remove the offset (or set it to 0 column offsets).

I assume that color_key and target_Range are named ranges in your excel sheets (somewhere).

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

The code CELL.Interior.Color returns an integer code that represents the cell fill color, as you mentioned you need the exact same color.

A dictionary dict works by reading in (key, value) pairs using the syntax dict.Add key, value. And returns value when it is passed the corresponding key: dict(key)=value.

Excel vba conditional formatting using dynamic color key

1 Answers