Bdtyktl

I have a table that contains up to about 100 different text values and I want to color them according to the text so that you can visually see what cells have the same text (table!B2:DB100).

I have a separate sheet that lists the values that are in the table (dynamically) (list!A1:A100).

A possible solution would be to apply a conditional formatting that figures out whether a cell in table!B2:DB100 is equal to list!A1. If so color it with color 1. Then add another conditional formatting saying if a cell in table!B2:DB100 is equal to list!A2 then color is with color 2. And so on.

Doing this manually would be painful because there are 100 possible text values.

I believe I could do it with a macro which has the added advantage that you can apply RGB values in a nice orderly way.

Is a macro, in fact, the best way to do it? Will it bog down the spreadsheet to have ~100 conditional formattings applied? Is there a simpler way to achieve the same effect?

For what it's worth, I have something working reasonably well. I wrote a macro to apply the conditional formatting.

Sub conditional()

'

' conditional Macro



i = 1

station = 1

colorscheme = 1

Do Until Sheets("Data").Cells(i + 3, 3) = ""

    a = "=Data!" + Sheets("Data").Cells(i + 3, 3).Address

    c1 = 255

    c2 = 147 + 17 * station

    Select Case colorscheme

        Case 1 'red

            mycolor = RGB(c1, c2, c2)

        Case 2 'yellow

            mycolor = RGB(c1, c1, c2)

        Case 3 'green

            mycolor = RGB(c2, c1, c2)

        Case 4 'cyan

            mycolor = RGB(c2, c1, c1)

        Case 5 'blue

            mycolor = RGB(c2, c2, c1)

        Case 6 'purple

            mycolor = RGB(c1, c2, c1)

    End Select

    Range("H4:BE48").FormatConditions.Add Type:=xlTextString, String:=a, _

        TextOperator:=xlContains

    With Range("H4:BE48").FormatConditions(Range("H4:BE48").FormatConditions.Count)

        .Interior.Color = mycolor

        .StopIfTrue = True

    End With

    i = i + 1

    station = station + 1

    If station = 5 Then

        station = 1

        colorscheme = colorscheme + 1

    End If

    If colorscheme = 7 Then

        colorscheme = 1

    End If

Loop



End Sub

And here's how it looks in an example:

I'd welcome any comments, suggestions, or improvements.