Trying to change ColorIndex for series settings

[SiriS]

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

Please Login or Register  to view this content.

but since the colorIndex is xlAutomatic for the first series, all series only gets the xlAutomatic colorindex setting...

Any ideas??

Regards,
Siri

[Jon Peltier]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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).MarkerBackgroundColorIndex
> .MarkerForegroundColorIndex =
> ActiveChart.SeriesCollection(j).MarkerForegroundColorIndex
> .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
>

[SiriS]

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

[Jon Peltier]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>