Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Excel to use a custom RGB color in a chart series?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Excel to use a custom RGB color in a chart series?
Drew,
Don't let Excel choose which color to match, instead specify the ColorIndex that you want. After customizing the pallet... Sub SetSeriesColor() ActiveChart.SeriesCollection(1).Interior.ColorInde x = 17 ActiveChart.SeriesCollection(2).Interior.ColorInde x = 18 'and so on End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Drew Lettington" wrote in message 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Excel to use a custom RGB color in a chart series?
That will work fine as long as what's in the palette is known in advance.
Since I don't know in advance what is in the palette at any given index I need some way to search for a match unless someone knows of a better way to get the desired result. If I can find a way to get Excel to give me an exact match if the RGB value is in the palette, I can ask the user to load a palette containing his custom colors before I create the chart. If not, I guess my code will have to search the palette for find a match if that's possible. - Drew "Jim Cone" wrote: Drew, Don't let Excel choose which color to match, instead specify the ColorIndex that you want. After customizing the pallet... Sub SetSeriesColor() ActiveChart.SeriesCollection(1).Interior.ColorInde x = 17 ActiveChart.SeriesCollection(2).Interior.ColorInde x = 18 'and so on End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Drew Lettington" wrote in message 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Excel to use a custom RGB color in a chart series?
As you've already found out when you apply an RGB colour Excel will match to
the closest it finds in the palette and apply the colorindex that represents that colour. If, and I assume it is, it's a workbook you are distributing you don't need to concern yourself with the user's palette. The palette is tied to the workbook (not users application settings) so you can programmatically customize the palette as Jim suggested. Why not customize the automatic chart colours, bar type fills are 'automatically' applied in series order starting at colorindex 17, line types start from 25 myBook.colors(17) = RGB(89, 219, 176) Assuming you want the same colour scheme for all your charts / series orders you only need to customize the palette once and forget about formatting individual series in new charts. If you go that route and want to reset what you previously changed For Each sr In ActiveChart.SeriesCollection sr.Interior.ColorIndex = xlAutomatic Next Regards, Peter T "Drew Lettington" wrote in message ... That will work fine as long as what's in the palette is known in advance. Since I don't know in advance what is in the palette at any given index I need some way to search for a match unless someone knows of a better way to get the desired result. If I can find a way to get Excel to give me an exact match if the RGB value is in the palette, I can ask the user to load a palette containing his custom colors before I create the chart. If not, I guess my code will have to search the palette for find a match if that's possible. - Drew "Jim Cone" wrote: Drew, Don't let Excel choose which color to match, instead specify the ColorIndex that you want. After customizing the pallet... Sub SetSeriesColor() ActiveChart.SeriesCollection(1).Interior.ColorInde x = 17 ActiveChart.SeriesCollection(2).Interior.ColorInde x = 18 'and so on End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Drew Lettington" wrote in message 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Excel to use a custom RGB color in a chart series?
I thought simplifiying the issue would make it easier but I guess not. My actual code is part of an Excel add-in that renders charts in users' workbooks. The chart specification may contain any custom color values for the series and there may be multiple charts in a single workbook with different custom colors. When my code runs the palette may contain any set of colors; my add-in doesn't control the state of the palette when the chart rendering starts. What I would like to do is use the actual custom colors if they exist in the palette. I was surprised that Excel wouldn't do that automatically when I set an RGB value that does exist in the palette. Since that is not the case I'd like to have my code manually check for an exact match. Or, have I misunderstood how Excel color matching works? - Drew "Peter T" wrote: As you've already found out when you apply an RGB colour Excel will match to the closest it finds in the palette and apply the colorindex that represents that colour. If, and I assume it is, it's a workbook you are distributing you don't need to concern yourself with the user's palette. The palette is tied to the workbook (not users application settings) so you can programmatically customize the palette as Jim suggested. Why not customize the automatic chart colours, bar type fills are 'automatically' applied in series order starting at colorindex 17, line types start from 25 myBook.colors(17) = RGB(89, 219, 176) Assuming you want the same colour scheme for all your charts / series orders you only need to customize the palette once and forget about formatting individual series in new charts. If you go that route and want to reset what you previously changed For Each sr In ActiveChart.SeriesCollection sr.Interior.ColorIndex = xlAutomatic Next Regards, Peter T "Drew Lettington" wrote in message ... That will work fine as long as what's in the palette is known in advance. Since I don't know in advance what is in the palette at any given index I need some way to search for a match unless someone knows of a better way to get the desired result. If I can find a way to get Excel to give me an exact match if the RGB value is in the palette, I can ask the user to load a palette containing his custom colors before I create the chart. If not, I guess my code will have to search the palette for find a match if that's possible. - Drew "Jim Cone" wrote: Drew, Don't let Excel choose which color to match, instead specify the ColorIndex that you want. After customizing the pallet... Sub SetSeriesColor() ActiveChart.SeriesCollection(1).Interior.ColorInde x = 17 ActiveChart.SeriesCollection(2).Interior.ColorInde x = 18 'and so on End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Drew Lettington" wrote in message 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Excel to use a custom RGB color in a chart series?
What I would like to do is use the actual custom colors if they exist in
the palette. I was surprised that Excel wouldn't do that automatically when I set an RGB value that does exist in the palette. Since that is not the case- With cells Excel will do exactly that, either apply the exact RGB if it exists or the nearest matched colour (according to Excel's colour match algorithm). With charts Excel may or may not do the same type of match, there are two anomalies 1 Colour match is compared with the default palette, not the current possibly customized palette. 2 Having found a match (exact or nearest) the colorindex of the matched 'default' colour is applied, ie not the colorindex of even an identical colour that might exist in a different location in a customized palette. IOW applying an RGB as a chart colour format unreliably applies the exact or nearest colour with a customized palette. For your purposes if you want to know if your RGB exists in the current palette c = RGB(r,g,b) vPal = activeworkbook.colors for i = 1 to 56 if vPal(i) = c then exit for end if if i < 57 then idx = i ' exact match end if If the above returns an exact match (idx < 0) apply idx as the colorindex (not RGB colour) to your series. If not and you want to make use of Excel's colour match to apply to a chart ThisWorkbook.colors = activeworkbook.colors ' (thisworkbook = your addin) with thisworkbook.worksheets(1).range("A1").interior ..color = RGB(r,g,b) idx = .colorindex end with Your unknown now is how close is the matched colour to your RGB. Could compare by summing the absolute differences of the individual rgb components though this method is even more simplistic than Excel's (colour difference exists in a non-linear 3D space). Regards, Peter T "Drew Lettington" wrote in message ... I thought simplifiying the issue would make it easier but I guess not. My actual code is part of an Excel add-in that renders charts in users' workbooks. The chart specification may contain any custom color values for the series and there may be multiple charts in a single workbook with different custom colors. When my code runs the palette may contain any set of colors; my add-in doesn't control the state of the palette when the chart rendering starts. What I would like to do is use the actual custom colors if they exist in the palette. I was surprised that Excel wouldn't do that automatically when I set an RGB value that does exist in the palette. Since that is not the case I'd like to have my code manually check for an exact match. Or, have I misunderstood how Excel color matching works? - Drew "Peter T" wrote: As you've already found out when you apply an RGB colour Excel will match to the closest it finds in the palette and apply the colorindex that represents that colour. If, and I assume it is, it's a workbook you are distributing you don't need to concern yourself with the user's palette. The palette is tied to the workbook (not users application settings) so you can programmatically customize the palette as Jim suggested. Why not customize the automatic chart colours, bar type fills are 'automatically' applied in series order starting at colorindex 17, line types start from 25 myBook.colors(17) = RGB(89, 219, 176) Assuming you want the same colour scheme for all your charts / series orders you only need to customize the palette once and forget about formatting individual series in new charts. If you go that route and want to reset what you previously changed For Each sr In ActiveChart.SeriesCollection sr.Interior.ColorIndex = xlAutomatic Next Regards, Peter T "Drew Lettington" wrote in message ... That will work fine as long as what's in the palette is known in advance. Since I don't know in advance what is in the palette at any given index I need some way to search for a match unless someone knows of a better way to get the desired result. If I can find a way to get Excel to give me an exact match if the RGB value is in the palette, I can ask the user to load a palette containing his custom colors before I create the chart. If not, I guess my code will have to search the palette for find a match if that's possible. - Drew "Jim Cone" wrote: Drew, Don't let Excel choose which color to match, instead specify the ColorIndex that you want. After customizing the pallet... Sub SetSeriesColor() ActiveChart.SeriesCollection(1).Interior.ColorInde x = 17 ActiveChart.SeriesCollection(2).Interior.ColorInde x = 18 'and so on End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Drew Lettington" wrote in message 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
Combining Multiple-Color Data Series into One Bar Chart | Charts and Charting in Excel | |||
Why Color Flled Data Series on Line Chart Slants | Charts and Charting in Excel | |||
help me to change the color of two series in a chart into the same | Charts and Charting in Excel | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel |