Custom colours in charts

's Computer Specifications

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>
-------------------------------------------------------------------

Suggestions welcome!

's Computer Specifications

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"DuncanL" <(E-Mail Removed)> wrote in message
news:5AB63F71-3ED1-4C10-911B-(E-Mail Removed)...
> 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>
> 199);"> 
> 173);"> 
> 218)"> 
> 190)"> 
> 114)"> 
> 43)"> 
> </body>
> </html>
> -------------------------------------------------------------------
>
> Suggestions welcome!

's Computer Specifications

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

's Computer Specifications

> 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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"DuncanL" <(E-Mail Removed)> wrote in message
news:092F924D-2F34-436C-9AB2-(E-Mail Removed)...
> 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

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I customise colours used in charts and keep those colours.	LJ	Microsoft Excel Charting	2	20th May 2010 01:50 PM
can you set custom colours to be used in excel charts	=?Utf-8?B?a2F0ZXQ=?=	Microsoft Excel Charting	6	19th Jun 2009 03:11 AM
Charts - Legend Colours	=?Utf-8?B?UGF1bCBEZW5uaXM=?=	Microsoft Access Reports	5	19th Oct 2006 05:09 PM
Charts - Legend Colours	=?Utf-8?B?UGF1bCBEZW5uaXM=?=	Microsoft Access Reports	1	12th Oct 2006 09:44 PM
Colours in Bar Charts	=?Utf-8?B?UmljaGFyZCBIdXRjaGluc29u?=	Microsoft Excel Charting	1	13th Nov 2003 02:39 AM