Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selection.Interior.ColorIndex (2002)

    Selection.Interior.ColorIndex = ??????

    I have a cell fill color in a worksheet that is non standard. How can I find out it's index number? Or, how can I use it in code?

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection.Interior.ColorIndex (2002)

    pls see attached the index numbers and its related colors
    try
    Selection.Interior.ColorIndex = 3
    I read somewhere, excel recognise some 8 colors, such as red, blue, green.....
    but can't be sure of this. pls find out from others or thru the microsoft KB

    HTH
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection.Interior.ColorIndex (2002)

    Hi Nathan

    You could right click a sheet tab and then view code and copy and paste this:-

    Sub DisplayPallet()
    Dim N As Long
    For N = 1 To 56
    Cells(N, 1).Interior.ColorIndex = N
    Next N
    End Sub

    Go back to the sheet 'Alt F8' and select the macro DisplayPallet and it will colour all of the cells from A1 to A56 with the pallette options, the number then corrosponds to the colour when you are calling it in code for example ColourIndex 1 will be black, ColourIndex 2 white,ColourIndex 3 red etc.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection.Interior.ColorIndex (2002)

    Thanks both.

    This however is a NON STANDARD colour, it is not one of the 56.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Selection.Interior.ColorIndex (2002)

    If it is a non standard color that is not in the list of 56 then it does not have a color index. There are only 56 colors in the index and 16.8 million possible colors...

    You could use the COLOR property instead and define the color with the RGB function. This gives a rose color:

    selection.interior.color = rgb(255,125,125)

    You can get the RGB values using Tools - options - color(tab) - [modify] - custom(tab) choose the color and the RGB values are listed. Then [Cancel][cancel] to not save the modified color.

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection.Interior.ColorIndex (2002)

    Thanks Steve, but I still can't work this out. I am attaching a worksheet with the colour.
    Attached Files Attached Files

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Selection.Interior.ColorIndex (2002)

    The colorindex of those cells is 17.
    Note also that the interior fill of a cell has to be one of the 56 colours in the palette for that workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Selection.Interior.ColorIndex (2002)

    Try the line of code:
    selection.interior.color= rgb(153,153,255)

    to get the color in your example

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection.Interior.ColorIndex (2002)

    Using the selection.interior.color= rgb(153,153,255) method Steve mentions results in Excel picking the closest color for you RGB values, not the actual color. (153,153,125) is one of Excel's standard colors. Color index 17 as Rory had pointed out.

    If you want to use a non standard color you have to replace one of the 56 standard colors. The following replaces color number 17:

    sub replace17()
    ActiveWorkbook.Colors(17) = RGB(125, 0, 75)
    end sub


    If you run the following you can see the difference between the two methods

    Sub ShowColorDiff()

    Selection.Interior.Color = RGB(125, 0, 75)
    ActiveWorkbook.Colors(3) = RGB(125, 0, 75)
    Selection.Offset(1, 0).Interior.ColorIndex = 3

    End Sub

    The attached picture show the difference between results of the two methods. The top color is the closest match to (125,0,75) that Excel uses, but is actually (102,0,102)
    Attached Images Attached Images

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection.Interior.ColorIndex (2002)

    Gentlemen, me thinks me is loosing the plot!!
    The colour certainly is #17.
    I have never taken much notice in the past, I assumed that all the standard colours available were in the fill colour dropdown on the formatting toolbar, only now that I notice that it only shows 40/56. So how are the other 16 standard colours (easily) accessed, outside of the VBE?

    How is 17, being a chart fill colour accessed as a cell fill?
    Attached Images Attached Images

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Selection.Interior.ColorIndex (2002)

    But the 56 are not all standard colors. They are editable for each and every workbook. The number is an index value that has no association for a color only a lookup in the settings for that workbook. If you want a particular color use the COLOR property not the ColorIndex Property.

    The 16 not listed for in the fill values are the defaults for the charts (the fills and the lines) and can be set according to what you want the series on charts to have for default colors.

    If you want that particular color in the fill or text color pallette just edit one of them to be that color and it will be there in that workbook. If you want it for every workbook, save it as a template named book.xlt in XLStart folder

    Steve

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection.Interior.ColorIndex (2002)

    OK. Thanks.

    I was confused by the fact that a specific workbook had a cell fill colour that was not in the "FILL COLOUR" dropdown (40) on the formatting toolbar.

    I first thought that it was not one of excel's standard colours, but I found out (here) that it was, index number 17.

    I now realise that the extra 16 colours are available to use as cell fill by using the Format | Cells | Patterns route, where all 56 are available to use. (Now known affectionately as the very annoying long way round <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

    .Interior.Color = vbRed 'One of the eight VBA color constants

    .Interior.Color = RGB(153, 153, 255) 'Fixed version of ColourIndex = 17 (In this specific workbook)

    I'm happily less confused. 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
  •