Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Colorindex (O2003/WinXP)

    Hi,

    I had a problem with the customisation of the colorpalette.
    - so I wanted to check what was actually in there - the setting for each color in the pallette.

    Thought I could loop through each colour - print its index number in column 1 and apply the actual colour in column 2.
    - so I wrote a little piece of code to do this...

    ...but - when I Iooked at the numbers in column one, they looked very odd... - and yet again not that much really.. eg. black was 0, white 255...

    Turned to the help (VBA) on "Colorindex", but haven't been able to find a good explanation.

    My code is here...

    Public Sub ColorIndex_Publish()
    Dim wsColor As Worksheet
    Dim c

    Set wsColor = Worksheets(1)
    wsColor.Activate
    Range("A1").Select
    For c = 1 To 100
    With Selection
    .Value = ActiveWorkbook.Colors©
    .Offset(0, 1).Interior.ColorIndex = c
    .Offset(0, 1).Interior.Pattern = xlSolid
    .Offset(1, 0).Select
    End With
    Next c

    End Sub



    1. Can anyone tell me what these odd numbers are?
    2. How do I extract the index number and the associated color into a sheet?


    Thanks
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

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

    Re: Colorindex (O2003/WinXP)

    Colors© returns the RGB color value. Black = 0, Red = 255, Green = 256 * 255 = 65280 and Blue = 256 * 256 * 255 = 16711680. Instead of

    .Value = ActiveWorkbook.Colors©

    simply use

    .Value = c

    Moreover, since the Excel palett contains only 56 colors, you should use For c = 1 To 56.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Colorindex (O2003/WinXP)

    Hi Hans,

    Thanks - makes a lot of sense, but something still puzzles me.

    This bit of code illustrates the problem:
    (ce and HeadingRows are objects, ce is a single cell, HeadingRows a range)


    THIS DOES NOT WORK - FOR SOME ODD REASON

    For Each ce In HeadingRows
    With ce
    .Interior.Color = RGB(204,216,222) (THIS GIVES AN INCORRECT COLOR)
    With .Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Color = RGB(255, 255, 255)
    .Weight = xlThin
    End With
    End with
    Next ce

    ....on the other hand, if I first modifies my colorpalette through book.xlt, so that color 54 is equivalent to RGB(204,216,222) and then applies
    my colorpalette to the book in question and then sets the code to use .colorindex = 54 instead - then...

    THIS DOES WORK - ALSO FOR SOME ODD REASON

    For Each ce In HeadingRows
    With ce
    .Interior.ColorIndex = 54
    With .Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Color = RGB(255, 255, 255)
    .Weight = xlThin
    End With
    End with
    Next ce

    So.... - then I can get my colour as I want it. - Any logical explanations on this???
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

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

    Re: Colorindex (O2003/WinXP)

    Excel has a 56 color palette that is stored with the workbook. In a single workbook, you can only use the 56 colors from the palette - if you try to set an RGB color that is not in the palette, Excel will use the nearest color from the palette. You can, however, modify the colors in the palette to whatever you like. Excel will then use the colors you assigned to the palette, but the original colors will become unavailable in that workbook.

    In short, you can use any of the 16777216 RGB colors in Excel, but only 56 of them in a single workbook.

Posting Permissions

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