Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Interior.ColorIndex (Excel)

    I can't find a list of regular constants for colourIndex. Am I barmy? Didn't we used to have built-in constants like "Blue" and "Orange"?
    <pre>Sub test()
    Debug.Print ActiveCell.Interior.ColorIndex ' Orange = 46
    End Sub</pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Interior.ColorIndex (Excel)

    The only constants with a fixed meaning are xlColorIndexAutomatic (-4105) and xlColorIndexNone (-4142). The colors associated with the other possible values 1 ... 56 depend on the color palette. This can be set for each workbook individually. The default color for ColorIndex = 3 is red, but it could be any other color if the color palette has been edited.

    The ColorIndex values are *not* RGB colors.

    For many objects, it is possible to set the RGB color by using the Color property. Because Excel uses a 56 color palette, the color will be mapped to the nearest equivalent in the workbook's palette.
    So you can set

    ActiveCell.Interior.ColorIndex = 3

    or

    ActiveCell.Interior.Color = vbRed

    or

    ActiveCell.Interior.Color = RGB(255, 0, 0)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Interior.ColorIndex (Excel)

    Thank you, Hans.
    I was confused between ColorIndex and Color.
    Client macros have too many literals and I'm replacing them with dynamic values.
    I suspect that .Color might have been a better choice (for them) than .ColorIndex.

    I've already got rid of all the "Global" statements.

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Interior.ColorIndex (Excel)

    I have been messing with this stuff awhile and came up with these arrays:
    <pre> ColorNames = Array("Red", "Bright Green", "Blue", "Yellow", "Pink", "Aqua", "Olive", "Teal", "Grey 25%", "Grey 40%", _
    "Grey 50%", "Purple", "Plum", "Maize", "Light Blue", "Light Purple", "Fushia", "Bright Yellow", "Light Blue", _
    "Light Turquoise", "Light Green", "Light Yellow", "Pale Blue", "Rose", "Lavender", "Tan", "Aqua", "Lime", _
    "Gold", "Light Orange", "Orange", "Brown")
    ColorNumbs = Array(2, 3, 4, 5, 6, 7, 11, 13, 14, 47, 15, 16, 17, 18, 19, 23, 25, 26, 32, _
    33, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 52)</pre>

    Hope it helps.
    Alan

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Interior.ColorIndex (Excel)

    > Hope it helps.
    It does, thanks.

Posting Permissions

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