| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom colours in charts
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 ------------------------------------------------------------------- Suggestions welcome! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom colours in charts
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.ColorInde x = 45 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DuncanL" wrote in message ... 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 ------------------------------------------------------------------- Suggestions welcome! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom colours in charts
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom colours in charts
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).bord er.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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I customise colours used in charts and keep those colours. | Charts and Charting in Excel | |||
| can you set custom colours to be used in excel charts | Charts and Charting in Excel | |||
| Consistent colours between worksheet charts? | Charts and Charting in Excel | |||
| Custom colours | Excel Discussion (Misc queries) | |||
| Bubble Charts Colours go wrong | Charts and Charting in Excel | |||
Linear Mode
