Custom colours in charts

DuncanL · Oct 25, 2007

Why is Excel ignoring my custom colours for chart lines and fills, and
picking something a bit (but not very) similar instead? Sea green is blue,
lavender becomes grey...

This example has been cobbled together using an MS TechNet example

-------------------------------------------------------------------
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = "Operating System"
objWorksheet.Cells(2,1) = "Windows Server 2003"
objWorksheet.Cells(3,1) = "Windows XP"
objWorksheet.Cells(4,1) = "Windows 2000"

objWorksheet.Cells(1,2) = "Number of Computers"
objWorksheet.Cells(2,2) = 545
objWorksheet.Cells(3,2) = 987
objWorksheet.Cells(4,2) = 611

objWorksheet.Cells(1,3) = "Something Else"
objWorksheet.Cells(2,3) = 432
objWorksheet.Cells(3,3) = 278
objWorksheet.Cells(4,3) = 495

objWorksheet.Cells(1,4) = "Another thing"
objWorksheet.Cells(2,4) = 832
objWorksheet.Cells(3,4) = 458
objWorksheet.Cells(4,4) = 921

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

With objExcel.ActiveChart
..ChartType = -4100

..SeriesCollection(1).Interior.Color = RGB(144, 211, 199)
..SeriesCollection(1).Border.Color = RGB(93, 191, 173)
..SeriesCollection(1).Border.Weight = 4

..SeriesCollection(2).Interior.Color = RGB(190, 186, 218)
..SeriesCollection(2).Border.Color = RGB(140, 133, 190)
..SeriesCollection(2).Border.Weight = 4

..SeriesCollection(3).Interior.Color = RGB(251, 128, 114)
..SeriesCollection(3).Border.Color = RGB(249, 64, 43)
..SeriesCollection(3).Border.Weight = 4

End With
-------------------------------------------------------------------

The following (rubbish) HTML saved as a file will show the colours I expect:
-------------------------------------------------------------------
<html>
<body>
 
 
 
 
 
 
</body>
</html>

Jon Peltier · Oct 25, 2007

Excel has a palette of 56 colors. When you use RGB to define a color, Excel
uses the element in the palette which it decides is closes to the RGB you
specify.

You could assign the RGB to color in the palette, then use this color index
to color your chart element.

For example:

ActiveWorkbook.Colors(45) = RGB(144, 211, 199)
ActiveChart.SeriesCollection(1).Interior.ColorIndex = 45

- Jon

DuncanL · Oct 26, 2007

Jon,

Excel has a palette of 56 colors. When you use RGB to define a color, Excel
uses the element in the palette which it decides is closes to the RGB you
specify.

So what on earth is the point of allowing RGB colours if it then
completely ignores them? If you're going to provide functions that accept
RGB, it is madness to then cripple that so it is fundamentally useless. And
Trendlines will only accept RGB colours, not indexes, so there is no way to
set them to an exact colour.

It does seem a bit odd, given that true colour displays have been around
for many, many years now that Excel is stuck with such a limited palette.
Does anyone who wants a good looking chart just use something else instead?

I realise that this is not your fault and you can't do anything about it,
but I'm just having a small rant here! ;-)

You could assign the RGB to color in the palette, then use this color index
to color your chart element.

Well that works sort of works (barring the Trendlines), so thank you for
that.

Thanks for the help

Duncan

Jon Peltier · Oct 27, 2007

So what on earth is the point of allowing RGB colours if it then

completely ignores them?

It is what it is. Excel didn't recognize a continuous palette of colors
until 2007. VBA came along later than Excel's color palette, when RGB was a
standard. The VBA/Excel couple does not completely ignore RGB, it merely
tries to match an RGB to the palette as closely as it can.

And Trendlines will only accept RGB colours, not indexes, so there is no
way to
set them to an exact colour.

This works for me:

activechart.SeriesCollection(1).trendlines(1).border.colorindex=3

Does anyone who wants a good looking chart just use something else
instead?

We modify our palette, as I discussed later in my response. And it even
works with trendlines.

- Jon

Problem seeing some characters when sending email to Outlook client	60	Oct 15, 2010
pages don't work in Firefox?	2	Jan 12, 2010
CSS Problems	9	Aug 17, 2008
HTML showing up in code	3	Jan 16, 2009
rendering owc charts in asp.net page	3	Sep 4, 2005
Menu in CSS problems	9	Nov 12, 2008
Hyperlinks NOT underlined in website using CSS	3	Mar 12, 2007
Aligning trouble	4	Feb 15, 2006

Custom colours in charts

DuncanL

Jon Peltier

DuncanL

Jon Peltier

Ask a Question

Similar Threads