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