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" wrote in
message ...
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
"Jim Cone" wrote:
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.ColorInde x = 17
ActiveChart.SeriesCollection(2).Interior.ColorInde x = 18
'and so on
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Drew Lettington"
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