Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting ColorIndex (2003)

    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!

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting ColorIndex (2003)

    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!

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting ColorIndex (2003)

    Try this code:

    <pre>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

    </pre>


  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting ColorIndex (2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •