Setting Background Color RGB (Always goes to closest index color)

[ryanmhuc]

I'm trying to set the background color of cells in excel 2003.

target.Interior.Color = RGB(228, 234, 244)

Except no matter what color I use it seems to default to the closest
index color. So the above goes to color

RGB(204, 255, 255) which is ColorIndex 20. How I can set it to a custom
RGB color?

Thanks

 

[Alok]

Excel only allows you to set one of the colors in its color pallette.
Alok

 

[ryanmhuc]

Thanks alok. But then why does microsoft imply you can?? On this page:

http://office.microsoft.com/en-us/assistance/HA011366271033.aspx

They set it to a color thats not in the Palette [RGB(200,160,35)] ??

I have also found numerous other sites that say you can set it to any
color you wish using interior.color = RGB(int, int, int)

Are they all wrong??

 

[Tom Ogilvy]

You can set it to any color you want - but Excel will convert it to one of
the 56 colors defined in the palette.

In excel help under worksheet and workbook specifications:

Colors in a workbook 56


That said, you can set any of the 56 colorindexes to any one of the RGB
colors (modify the palette). So you can have any color you want in the
cell, but within all the cells, you are limited to a total of 56 different
colors.

 

[ryanmhuc]

Thanks Tom for the explanation. I have to admit thats pretty stupid
having it limited though. I'm sure there a good reason behind it?

 

[Tom Ogilvy]

You must store the setting for every used cell in the workbook. the space
required to store the index number is significantly smaller than the space
required for an rgb color.

 

[Peter T]

As has been confirmed to you Excel maps colours that don't exist in the
palette to nearest (?) colour in the palette. It has also been suggested you
can customize your own colour.

However if you don't want to customize the palette you could use one of the
pattern shades combining two colours to get a reasonably close colour match.

As it happens, I have a colour match algorithm which can be used, amongst
other purposes, to return the best 5 permutations of interior + pattern
colours and shade to match a custom colour without customizing the palette.

For your RGB(228, 234, 244) - returns the following 5 suggestions -

Sub MyBlue()
Dim i As Long
Dim vIntIdx, vPattern, vPatIdx

' match RGB(228, 234, 244)
vIntIdx = Array(34, 2, 2, 2, 24)
vPatIdx = Array(38, 37, 24, 47, 36)
vPattern = Array(xlGray25, xlGray25, xlGray25, xlGray25, xlGray25)

For i = 0 To 4
With Cells(i + 2, 2).Interior
.ColorIndex = vIntIdx(i)
.Pattern = vPattern(i)
.PatternColorIndex = vPatIdx(i)
End With
Next

With ActiveSheet.Shapes.AddShape(1, 150, 15#, 150#, 65#)
.Fill.ForeColor.RGB = RGB(228, 234, 244)
End With

End Sub

Un-typically, none of these are a particularly close match and unfortunately
none with 50% shade (preferable to 25% or 75%).

I understand in the pending Excel 12 it will be possible to format cells
with any colour.

Regards,
Peter T