Custom colours in charts

G

Guest

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>
<p style="width:100%;margin:0;padding:0;background-color:RGB(144, 211,
199);"> </p>
<p style="width:100%;margin:0;padding:0;background-color:RGB(93, 191,
173);"> </p>
<p style="width:100%;margin:0;padding:0;background-color:RGB(190, 186,
218)"> </p>
<p style="width:100%;margin:0;padding:0;background-color:RGB(140, 133,
190)"> </p>
<p style="width:100%;margin:0;padding:0;background-color:RGB(251, 128,
114)"> </p>
<p style="width:100%;margin:0;padding:0;background-color:RGB(249, 64,
43)"> </p>
</body>
</html>
 
J

Jon Peltier

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
 
G

Guest

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.
Click to expand...

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.
Click to expand...

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

Thanks for the help


Duncan
 
J

Jon Peltier

So what on earth is the point of allowing RGB colours if it then
completely ignores them?
Click to expand...

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.
Click to expand...

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?
Click to expand...

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

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

pages don't work in Firefox? 2
CSS Problems 8
CSS problem in Firefox 4
Parsing some info out of HTML - can you help me please? 3
FF placed image at the top of the <p> rather in its vertical middle 3
HTML showing up in code 3
Menu in CSS problems 9
Hyperlinks NOT underlined in website using CSS 3

Top