Results 1 to 9 of 9

Color Index

This is a discussion on Color Index within the Excel Questions forums, part of the Question Forums category; Is there a way to find out (aside from brute force) what the color index number is for a particular ...

  1. #1
    Hap
    Hap is offline
    Board Regular
    Join Date
    Jul 2005
    Posts
    611

    Default Color Index

    Is there a way to find out (aside from brute force) what the color index number is for a particular color? (Help hasn't been too helpful!!)

  2. #2
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,478

    Default

    try this from chip pearson's site http://www.cpearson.com/excel/colors.htm

    Code:
    Function CellColorIndex(InRange As Range, Optional _
    OfText As Boolean = False) As Integer
    '
    ' This function returns the ColorIndex value of a the Interior
    ' (background) of a cell, or, if OfText is true, of the Font in the cell.
    '
    Application.Volatile True
    If OfText = True Then
    CellColorIndex = InRange(1, 1).Font.ColorIndex
    Else
    CellColorIndex = InRange(1, 1).Interior.ColorIndex
    End If
    End Function

  3. #3
    Board Regular
    Join Date
    Sep 2002
    Location
    Lake Garda, Italy
    Posts
    328

    Default

    Hi Hap,

    Which help did you look in, Excel ?

    The VBA Reference help gives quite a lot lists with the various codes !

    Ciao,

    Don.
    If you start off on the wrong foot, you don't win the race.

  4. #4
    Hap
    Hap is offline
    Board Regular
    Join Date
    Jul 2005
    Posts
    611

    Default

    Thank you for the function. That is kinda cool.

    I have been using VBA and I cannot find any reference to the actual color index values.

  5. #5
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,478

    Default

    Quote Originally Posted by Hap
    Thank you for the function. That is kinda cool.

    I have been using VBA and I cannot find any reference to the actual color index values.
    here's some code to show a list of the 56 color index colors

    Code:
    Sub colors()
    For i = 1 To 56
      With Cells(i, "A")
        .Interior.ColorIndex = i
        .Value = i
        .HorizontalAlignment = xlCenter
        .Font.Color = vbWhite
        .Font.Bold = True
      End With
    Next i
    End Sub
    just run on a blank worksheet and it will fill up A1:A56 with the colors and place the number in the color (which essentially is the same as the row number)....hope this helps...

    ps...you can also reference some colors by doing

    .interior.color=vbRed

  6. #6
    Board Regular
    Join Date
    Oct 2004
    Posts
    3,478

    Default

    while i'm at it have a look at this function...from vba & macros from mr. excel himself....

    Code:
    Function CellColor(myCell As Range, Optional ColorIndex As Boolean)
        Dim myColor As String, IndexNum As Integer
        Select Case myCell.Interior.ColorIndex
        Case 1
            myColor = "Black"
            IndexNum = 1
        Case 2
            myColor = "White"
            IndexNum = 2
        Case 3
            myColor = "Red"
            IndexNum = 3
        Case 4
            myColor = "Bright Green"
            IndexNum = 4
        Case 5
            myColor = "Blue"
            IndexNum = 5
        Case 6
            myColor = "Yellow"
            IndexNum = 6
        Case 7
            myColor = "Pink"
            IndexNum = 7
        Case 8
            myColor = "Turquoise"
            IndexNum = 8
        Case 9
            myColor = "Dark Red"
            IndexNum = 9
        Case 10
            myColor = "Green"
            IndexNum = 10
        Case 11
            myColor = "Dark Blue"
            IndexNum = 11
        Case 12
            myColor = "Dark Yellow"
            IndexNum = 12
        Case 13
            myColor = "Violet"
            IndexNum = 13
        Case 14
            myColor = "Teal"
            IndexNum = 14
        Case 15
            myColor = "Gray-25%"
            IndexNum = 15
        Case 16
            myColor = "Gray-50%"
            IndexNum = 16
        Case 33
            myColor = "Sky Blue"
            IndexNum = 33
        Case 34
            myColor = "Light Turquoise"
            IndexNum = 34
        Case 35
            myColor = "Light Green"
            IndexNum = 35
        Case 36
            myColor = "Light Yellow"
            IndexNum = 36
        Case 37
            myColor = "Pale Blue"
            IndexNum = 37
        Case 38
            myColor = "Rose"
            IndexNum = 38
        Case 39
            myColor = "Lavender"
            IndexNum = 39
        Case 40
            myColor = "Tan"
            IndexNum = 40
        Case 41
            myColor = "Light Blue"
            IndexNum = 41
        Case 42
            myColor = "Aqua"
            IndexNum = 42
        Case 43
            myColor = "Lime"
            IndexNum = 43
        Case 44
            myColor = "Gold"
            IndexNum = 44
        Case 45
            myColor = "Light Orange"
            IndexNum = 45
        Case 46
            myColor = "Orange"
            IndexNum = 46
        Case 47
            myColor = "Blue-Gray"
            IndexNum = 47
        Case 48
            myColor = "Gray-40%"
            IndexNum = 48
        Case 49
            myColor = "Dark Teal"
            IndexNum = 49
        Case 50
            myColor = "Sea Green"
            IndexNum = 50
        Case 51
            myColor = "Dark Green"
            IndexNum = 51
        Case 52
            myColor = "Olive Green"
            IndexNum = 52
        Case 53
            myColor = "Brown"
            IndexNum = 53
        Case 54
            myColor = "Plum"
            IndexNum = 54
        Case 55
            myColor = "Indigo"
            IndexNum = 55
        Case 56
            myColor = "Gray-80%"
            IndexNum = 56
        Case Else
            myColor = "Custom color or no fill"
        End Select
        'if the index number is desired or if the cell color was not
        'returned return the index number
        If ColorIndex = True Or myColor = "Custom color or no fill" Then
            CellColor = IndexNum
        Else
            CellColor = myColor
        End If
    End Function

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    1
    1Black1 37Pale Blue37
    2
    2White2 38Rose38
    3
    3Red3 39Lavender39
    4
    4Bright Green4 40Tan40
    5
    5Blue5 41Light Blue41
    6
    6Yellow6 42Aqua42
    7
    7Pink7 43Lime43
    8
    8Turquoise8 44Gold44
    9
    9Dark Red9 45Light Orange45
    10
    10Green10 46Orange46
    11
    11Dark Blue11 47Blue-Gray47
    12
    12Dark Yellow12 48Gray-40%48
    13
    13Violet13 49Dark Teal49
    14
    14Teal14 50Sea Green50
    15
    15Gray-25%15 51Dark Green51
    16
    16Gray-50%16 52Olive Green52
    17
    33Sky Blue33 53Brown53
    18
    34Light Turquoise34 54Plum54
    19
    35Light Green35 55Indigo55
    20
    36Light Yellow36 56Gray-80%56
    Sheet17

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  7. #7
    Board Regular SidBord's Avatar
    Join Date
    Aug 2004
    Location
    Morgan Hill, CA
    Posts
    346

    Default

    Here's something to fry your mind. There are actually two color palettes. There's the one that the code posted above will access. I'll call that the XLPalette. Then there's the one that you see when you click the "Font Color" or "Fill Color" buttons at the top your window. I'll call that the UsrPalette. Initially, they both have the same colors in the same positions. If you are a color freak (like I am), you will be unhappy with the initial selection, and you will alter one or more of the colors by clicking TOOLS->OPTIONS->COLOR, then making appropriate changes using the color dialog box. Once you have done that, the UsrPalette is no longer the same as the XLPalette! So you'll wonder why the code posted above isn't showing you what you see on the UsrPalette. To make things worse, I've actually moved some of my colors to differenct locations on the UsrPalette. There's no harm done in making changes, but the VBA code statements just won't report what you see. Note that changing colors on the UsrPalette does NOT affect the XLPalette. However, the reverse is not true. Changing colors on the XLPalette with VBA statements WILL change the UsrPalette.
    To supplement your original question, I would ask how do you get the color numbers or indexes from the UsrPalette using VBA statements?
    Dell Studio XPS 8000 with Windows 7 & Office 2007 and Norton Internet Security & Anti-Virus

    "The market will always do what it must
    ..to prove that the majority is wrong!"

    "Old Age and Treachery
    ....Will Always Win
    .Over Youth and Skill."

  8. #8
    Board Regular SidBord's Avatar
    Join Date
    Aug 2004
    Location
    Morgan Hill, CA
    Posts
    346

    Default

    Here's a couple of macros to help you with RGB conversions:
    Code:
    Sub ShowRGBNoColorComponents()
    'This macro prompts for an RGB color number, then displays
    'the three RGB color component.
    Dim Red%, Green%, Blue%, Msg$, RGBNo&, Title$
    Title = "ShowConvertRGBNoToComponents"
    Msg = "Enter the value of the RGB color number to be converted:"
    RGBNo = Application.InputBox(Msg, Title, Default:=0, Type:=1)       'Returns a boolean or string
    Red = RGBNo And 255
    Green = RGBNo \ 256 And 255
    Blue = RGBNo \ 256 ^ 2 And 255
    Msg = "The color components of RGB number '" & RGBNo & "' are:" & vbCr & _
              "     Red = '" & Red & "'      Green = '" & Green & "'       Blue = '" & Blue & "'"
    MsgBox Msg, , Title
    End Sub   'ShowRGBNoColorComponents'
    
    Sub ShowCellRGBColorComponents_Locator()
    Call ShowCellRGBColorComponents("F")
    End Sub
    Sub ShowCellRGBColorComponents(Optional FontOrInterior$ = "F")                '9/16/05
    'This macro looks at the font or interior color of the currently
    'selected cell, then displays all of the RGB color components.
    'Arg "FontOrInterior" must have a value of "F" or "I",
    'which determines if the font or the interior color is examined.
    Dim Msg$, RGBColorNo&, ColorName$, R%, G%, B%
    Const Title$ = "ShowColorRGBComponents"
    If Left(UCase(FontOrInterior), 1) = "F" _
        Then
            RGBColorNo = ActiveCell.Font.Color
            FontOrInterior = "font"
        Else
            RGBColorNo = ActiveCell.Interior.Color
            FontOrInterior = "interior"
        End If
    R = RGBColorNo And 255
    G = RGBColorNo \ 256 And 255
    B = RGBColorNo \ 256 ^ 2 And 255
    Select Case RGBColorNo
        Case 0:               ColorName = "Black or 'No Color'"
        Case 16777215:   ColorName = "White"
        Case 255:            ColorName = "Red"
        Case 65280:        ColorName = "Green"
        Case 65535:        ColorName = "Yellow"
        Case 16711680:   ColorName = "Blue"
        Case 14423060:   ColorName = "DkBlue"
        Case 16711935:   ColorName = "Magenta"
        Case 16776960:   ColorName = "Cyan"
        Case Else:           ColorName = "Unknown"
    End Select
    Msg = "For cell  '" & ActiveCell.Address(False, False) & "'" & vbCr & _
              "the RGB " & FontOrInterior & " color number is" & vbCr & _
              Space(5) & "'" & RGBColorNo & "'   (" & ColorName & ")." & vbCr & vbCr & _
              "The RGB " & FontOrInterior & " color component" & vbCr & _
              "numbers are:" & vbCr & _
              Space(5) & "Red     = '" & R & "'" & vbCr & _
              Space(5) & "Green = '" & G & "'" & vbCr & _
              Space(5) & "Blue    = '" & B & "'" & vbCr & vbCr & _
              "Standard RGB color numbers" & vbCr & _
              "are:" & vbCr & _
              Space(3) & "'No Color' is  0" & vbCr & _
              Space(3) & "Black         is  0" & vbCr & _
              Space(3) & "White         is  16777215" & vbCr & _
              Space(3) & "Red            is  255" & vbCr & _
              Space(3) & "Green        is  65280" & vbCr & _
              Space(3) & "Yellow       is  65535" & vbCr & _
              Space(3) & "Blue           is  16711680" & vbCr & _
              Space(3) & "DkBlue         is  14423060" & vbCr & _
              Space(3) & "Magenta    is  16711935" & vbCr & _
              Space(3) & "Cyan          is  16776960"
    MsgBox Msg, , Title
    End Sub   'ShowCellRGBColorComponents'
    Dell Studio XPS 8000 with Windows 7 & Office 2007 and Norton Internet Security & Anti-Virus

    "The market will always do what it must
    ..to prove that the majority is wrong!"

    "Old Age and Treachery
    ....Will Always Win
    .Over Youth and Skill."

  9. #9
    Hap
    Hap is offline
    Board Regular
    Join Date
    Jul 2005
    Posts
    611

    Default

    Very Very Nice!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com