How to get Excel to use a custom RGB color in a chart series?

[Drew Lettington]

I created a bar with six series and then set each series to a custom RGB
color. I can see that Excel attempts a match to the custom color in the
current palette which looks okay but not perfect. So I modified the palette
to include all six custom colors and then set the series colors again. This
time I expected to get the exact colors I wanted since they were in the
palette. However, I got the same approximation as the first time.

Can anyone explain why when I set an explicit RGB color, Excel doesn't match
to the same color if it's in the palette? Or, does anyone know how to get
the desired behavior? If the built-in matching doesn't work the only option
I can imaging is to loop through all the entries in the palette and get its
RGB value (is that possible?) and when I find a match set the ColorIndex
instead of the Color property. If there's no exact match then use Excel's
built-in algorithm.

Here's a sample of my test code:

Running this macro with default palette, Excel chooses an approximate color
match.

Sub SetSeriesColor()
'Sets bar chart series colors to explict RGB values
ActiveChart.SeriesCollection(1).Interior.Color = RGB(89, 219, 176)
ActiveChart.SeriesCollection(2).Interior.Color = RGB(163, 168, 107)
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 64, 53)
ActiveChart.SeriesCollection(4).Interior.Color = RGB(224, 105, 84)
ActiveChart.SeriesCollection(5).Interior.Color = RGB(164, 219, 89)
ActiveChart.SeriesCollection(6).Interior.Color = RGB(253, 55, 218)
End Sub

Running this macro adds my custom RGB values to Excel's palette.

Sub AddToPalette()
'Add custom colors to palette
ActiveWorkbook.Colors(17) = RGB(89, 219, 176)
ActiveWorkbook.Colors(18) = RGB(163, 168, 107)
ActiveWorkbook.Colors(19) = RGB(255, 64, 53)
ActiveWorkbook.Colors(20) = RGB(224, 105, 84)
ActiveWorkbook.Colors(21) = RGB(164, 219, 89)
ActiveWorkbook.Colors(22) = RGB(253, 55, 218)
End Sub

Running SetSeriesColor macro again, I expected to get my custom colors in
the bar chart but it looked the same as when the default palette was used.

- Drew

 

[Jim Cone]

Drew,
Don't let Excel choose which color to match, instead specify the ColorIndex
that you want. After customizing the pallet...

Sub SetSeriesColor()
ActiveChart.SeriesCollection(1).Interior.ColorIndex = 17
ActiveChart.SeriesCollection(2).Interior.ColorIndex = 18
'and so on
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Drew Lettington"
<[email protected]>
wrote in message
I created a bar with six series and then set each series to a custom RGB
color. I can see that Excel attempts a match to the custom color in the
current palette which looks okay but not perfect. So I modified the palette
to include all six custom colors and then set the series colors again. This
time I expected to get the exact colors I wanted since they were in the
palette. However, I got the same approximation as the first time.

Can anyone explain why when I set an explicit RGB color, Excel doesn't match
to the same color if it's in the palette? Or, does anyone know how to get
the desired behavior? If the built-in matching doesn't work the only option
I can imaging is to loop through all the entries in the palette and get its
RGB value (is that possible?) and when I find a match set the ColorIndex
instead of the Color property. If there's no exact match then use Excel's
built-in algorithm.

Here's a sample of my test code:

Running this macro with default palette, Excel chooses an approximate color
match.

Sub SetSeriesColor()
'Sets bar chart series colors to explict RGB values
ActiveChart.SeriesCollection(1).Interior.Color = RGB(89, 219, 176)
ActiveChart.SeriesCollection(2).Interior.Color = RGB(163, 168, 107)
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 64, 53)
ActiveChart.SeriesCollection(4).Interior.Color = RGB(224, 105, 84)
ActiveChart.SeriesCollection(5).Interior.Color = RGB(164, 219, 89)
ActiveChart.SeriesCollection(6).Interior.Color = RGB(253, 55, 218)
End Sub

Running this macro adds my custom RGB values to Excel's palette.

Sub AddToPalette()
'Add custom colors to palette
ActiveWorkbook.Colors(17) = RGB(89, 219, 176)
ActiveWorkbook.Colors(18) = RGB(163, 168, 107)
ActiveWorkbook.Colors(19) = RGB(255, 64, 53)
ActiveWorkbook.Colors(20) = RGB(224, 105, 84)
ActiveWorkbook.Colors(21) = RGB(164, 219, 89)
ActiveWorkbook.Colors(22) = RGB(253, 55, 218)
End Sub

Running SetSeriesColor macro again, I expected to get my custom colors in
the bar chart but it looked the same as when the default palette was used.

- Drew

 

[Drew Lettington]

That will work fine as long as what's in the palette is known in advance.
Since I don't know in advance what is in the palette at any given index I
need some way to search for a match unless someone knows of a better way to
get the desired result.

If I can find a way to get Excel to give me an exact match if the RGB value
is in the palette, I can ask the user to load a palette containing his custom
colors before I create the chart. If not, I guess my code will have to
search the palette for find a match if that's possible.

- Drew

 

[Peter T]

As you've already found out when you apply an RGB colour Excel will match to
the closest it finds in the palette and apply the colorindex that represents
that colour.

If, and I assume it is, it's a workbook you are distributing you don't need
to concern yourself with the user's palette. The palette is tied to the
workbook (not users application settings) so you can programmatically
customize the palette as Jim suggested.

Why not customize the automatic chart colours, bar type fills are
'automatically' applied in series order starting at colorindex 17, line
types start from 25

myBook.colors(17) = RGB(89, 219, 176)

Assuming you want the same colour scheme for all your charts / series orders
you only need to customize the palette once and forget about formatting
individual series in new charts.

If you go that route and want to reset what you previously changed

For Each sr In ActiveChart.SeriesCollection
sr.Interior.ColorIndex = xlAutomatic
Next

Regards,
Peter T

 

[Drew Lettington]

I thought simplifiying the issue would make it easier but I guess not. My
actual code is part of an Excel add-in that renders charts in users'
workbooks. The chart specification may contain any custom color values for
the series and there may be multiple charts in a single workbook with
different custom colors. When my code runs the palette may contain any set
of colors; my add-in doesn't control the state of the palette when the chart
rendering starts.

What I would like to do is use the actual custom colors if they exist in the
palette. I was surprised that Excel wouldn't do that automatically when I
set an RGB value that does exist in the palette. Since that is not the case
I'd like to have my code manually check for an exact match. Or, have I
misunderstood how Excel color matching works?

- Drew

 

[Peter T]

What I would like to do is use the actual custom colors if they exist in
the

palette. I was surprised that Excel wouldn't do that automatically when I
set an RGB value that does exist in the palette. Since that is not the

case-

With cells Excel will do exactly that, either apply the exact RGB if it
exists or the nearest matched colour (according to Excel's colour match
algorithm).

With charts Excel may or may not do the same type of match, there are two
anomalies

1 Colour match is compared with the default palette, not the current
possibly customized palette.
2 Having found a match (exact or nearest) the colorindex of the matched
'default' colour is applied, ie not the colorindex of even an identical
colour that might exist in a different location in a customized palette.

IOW applying an RGB as a chart colour format unreliably applies the exact or
nearest colour with a customized palette.

For your purposes if you want to know if your RGB exists in the current
palette

c = RGB(r,g,b)
vPal = activeworkbook.colors
for i = 1 to 56
if vPal(i) = c then exit for
end if

if i < 57 then
idx = i ' exact match
end if

If the above returns an exact match (idx <> 0) apply idx as the colorindex
(not RGB colour) to your series. If not and you want to make use of Excel's
colour match to apply to a chart

ThisWorkbook.colors = activeworkbook.colors ' (thisworkbook = your addin)

with thisworkbook.worksheets(1).range("A1").interior
..color = RGB(r,g,b)
idx = .colorindex
end with

Your unknown now is how close is the matched colour to your RGB. Could
compare by summing the absolute differences of the individual rgb components
though this method is even more simplistic than Excel's (colour difference
exists in a non-linear 3D space).

Regards,
Peter T