Formatting ColorIndex (2003)

Topic

New Reply

I have 7 conditions within a spreadsheet which I want to automatically change the fill color for. I know conditional formatting is limited to 3 conditions and so I have tried to copy and past the VB code from post 341,606, but I don’t know how I find out what colors are assigned to the color palette. Is there an easy way to find this out as I need specific colours and would prefer not to have to try trial and error!

Reply | Quote

Replies

Try this code:

Sub ColorTable()
' variables
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim sColorOrder As String
    Dim sLightColors As String
    Dim arColorOrder As Variant
    Dim iColorNr As Integer
    i = 0
    ' these are the colors in same order Excel shows
    ' them in the pulldown:
    sColorOrder = "1,53,52,51,49,11,55,56,9,46,12,10,14," & _
                  "5,47,16,3,45,43,50,42,41,13,48,7,44,6," & _
                  "4,8,33,54,15,38,40,36,35,34,37,39,2,17," & _
                  "18,19,20,21,22,23,24,25,26,27,28,29,30,31,32"
    arColorOrder = Split(sColorOrder, ",", , vbTextCompare)
    ' Light colors that will have a dark fontcolor:
    sLightColors = "|6|36|19|27|35|20|28|8|34|2|"
    Application.ScreenUpdating = False
    For j = 1 To 7            ' loop rows
        For k = 1 To 8            ' loop columns
            With Cells(j, k)
                iColorNr = arColorOrder(i)
                .Interior.ColorIndex = iColorNr
                .Value = iColorNr
                ' is the color light, then make the textcolor darker
                If InStr(1, sLightColors, "|" & iColorNr & "|") > 0 Then
                    .Font.ColorIndex = 56  'dark grey
                Else
                    .Font.ColorIndex = 2  'white
                End If
            End With
            i = i + 1
        Next k
    Next j
    ' Give it a nice layout:
    With Range(Cells(1, 1), Cells(7, 8))
        .RowHeight = 20
        .ColumnWidth = 4
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Bold = True
    End With
    Application.ScreenUpdating = True
End Sub

Reply | Quote

Perfect! Thanks! As it happens most of the numbers I needed were in the 40s and I waws just about getting there by trial and error, but this helps enormously!

Reply | Quote

EDIT: Finally figured out how to format the code. This sets up a table with each color and the number on the palette.

Reply | Quote