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

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

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

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