Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trying to change ColorIndex for series settings
Hi, I have a graph containing a variable amount of series with Border.ColorIndex=xlAutomatic. I would like the colorindex of the 4th series to be the same as the first series, the colorindex of the 5th series to be the same as the second series, the colorindex of the 6th series to be the same as the third series, the colorindex of the 7th series to be the same as the first series, the colorindex of the 8th series to be the same as the second series, and so on. The periodicity of the colorindex varies, I mean, it varies how many colors that shall be used. How to do this in VBA code? I have tried this Code: -------------------- For i = 1 To numOfSeries For j = 1 To numOfDifferentColors If i numOfDifferentColors Then ActiveChart.SeriesCollection(numOfDifferentColors * (i - 1) + j).Select With Selection.Border .ColorIndex = ActiveChart.SeriesCollection(j).Border.ColorIndex End With With Selection .MarkerBackgroundColorIndex = ActiveChart.SeriesCollection(j).MarkerBackgroundCo lorIndex .MarkerForegroundColorIndex = ActiveChart.SeriesCollection(j).MarkerForegroundCo lorIndex .MarkerStyle = ActiveChart.SeriesCollection(j).MarkerStyle End With End If Next Next -------------------- but since the colorIndex is xlAutomatic for the first series, all series only gets the xlAutomatic colorindex setting... Any ideas?? Regards, Siri -- SiriS ------------------------------------------------------------------------ SiriS's Profile: http://www.excelforum.com/member.php...fo&userid=7995 View this thread: http://www.excelforum.com/showthread...hreadid=521686 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trying to change ColorIndex for series settings
Hi Siri -
You need to do some detective work. First, go to Tools menu, Options, Color tab. Notice the two bottom rows are set aside for charts? Now, to determine the color index of these points, turn on the macro recorder, and use the Format Cells dialog to color the interior of a cell each color in the bottom row. From left to right (i.e., series index 1 to 8) I get: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 3/13/2006 by Jon Peltier ' ' With Selection.Interior .ColorIndex = 25 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With With Selection.Interior .ColorIndex = 26 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With ''' blah blah blah In any case, the color index numbers go from 25 to 32 for series 1 through 8. Early in your procedure, make an array like: Dim ColorIndexArray as Variant ColorIndexArray = Array(25, 26, 27, 28, 29, 30, 31, 32) Just remember that this is a 0-based array, so ColorIndexArray(1) = 26, not 25. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "SiriS" wrote in message ... Hi, I have a graph containing a variable amount of series with Border.ColorIndex=xlAutomatic. I would like the colorindex of the 4th series to be the same as the first series, the colorindex of the 5th series to be the same as the second series, the colorindex of the 6th series to be the same as the third series, the colorindex of the 7th series to be the same as the first series, the colorindex of the 8th series to be the same as the second series, and so on. The periodicity of the colorindex varies, I mean, it varies how many colors that shall be used. How to do this in VBA code? I have tried this Code: -------------------- For i = 1 To numOfSeries For j = 1 To numOfDifferentColors If i numOfDifferentColors Then ActiveChart.SeriesCollection(numOfDifferentColors * (i - 1) + j).Select With Selection.Border .ColorIndex = ActiveChart.SeriesCollection(j).Border.ColorIndex End With With Selection .MarkerBackgroundColorIndex = ActiveChart.SeriesCollection(j).MarkerBackgroundCo lorIndex .MarkerForegroundColorIndex = ActiveChart.SeriesCollection(j).MarkerForegroundCo lorIndex .MarkerStyle = ActiveChart.SeriesCollection(j).MarkerStyle End With End If Next Next -------------------- but since the colorIndex is xlAutomatic for the first series, all series only gets the xlAutomatic colorindex setting... Any ideas?? Regards, Siri -- SiriS ------------------------------------------------------------------------ SiriS's Profile: http://www.excelforum.com/member.php...fo&userid=7995 View this thread: http://www.excelforum.com/showthread...hreadid=521686 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trying to change ColorIndex for series settings
Hi Jon, thank you for your advice! Yes, it is a good idea to use an array of colorindex, of course! I suppose I only have to decide what first index to use. But the first index is different on different computers, I suppose? When I recorded the different colorindex I got the resulting index 17-24, not same as you (25-32). Regards, Siri -- SiriS ------------------------------------------------------------------------ SiriS's Profile: http://www.excelforum.com/member.php...fo&userid=7995 View this thread: http://www.excelforum.com/showthread...hreadid=521686 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trying to change ColorIndex for series settings
Aha. This is not different for different computers, it is different for
different chart types. I provided the values for the first 8 line or XY series (bottom row of palette), while you determined the values for the first 8 area, bar, or column series (one row higher in palette). Of course, using an array means you are not stuck with these ugly color choices, but you can choose your own. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "SiriS" wrote in message ... Hi Jon, thank you for your advice! Yes, it is a good idea to use an array of colorindex, of course! I suppose I only have to decide what first index to use. But the first index is different on different computers, I suppose? When I recorded the different colorindex I got the resulting index 17-24, not same as you (25-32). Regards, Siri -- SiriS ------------------------------------------------------------------------ SiriS's Profile: http://www.excelforum.com/member.php...fo&userid=7995 View this thread: http://www.excelforum.com/showthread...hreadid=521686 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change the settings on the row headers/ | Excel Worksheet Functions | |||
How do I change default number settings? | Excel Worksheet Functions | |||
Can you change the settings for an Import Wizard | Setting up and Configuration of Excel | |||
How do I change the default settings in charts? | Charts and Charting in Excel |