Change default series line color order

[Leonard Lan]

I am using VBA to create a set of line charts. The default line colors
include yellow or other light colors, which should be avoided. Is there a
method to change the default colors? Glad if anyone could give me a hand.

 

[JLatham]

I'm not sure if there's a way to change the default colors or not (I don't do
that much charting myself). But since you're using VBA to create your line
charts, you can control the colors for the data series in the code itself.
Probably the easiest way to see how to code it is to record a couple of
macros while changing the colors of a series or two and examine and modify it
to be used in your code that builds up the charts.

The recorded code is going to look something like this:
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 53
.Weight = xlMedium
.LineStyle = xlContinuous
End With

 

[Leonard Lan]

Yes, you have a good point. However, the number of data series is variable
in my function. It may not be approporiate to use ColorIndex property.
Probably, there is a way to set ColorIndex with a variable, but it will be
difficult to avoid light colors.

 

[JLatham]

If you know the upper limit for the number of series that may exist, then you
might set up an array to hold a series of .ColorIndex values with the array
sized to the max number of series. Then you could work through the series in
a loop and pick up a number from the array based on the series index number.
Not sure if this code would even work, but it could give you an idea. Might
look something like

For each anySeries in ActiveChart.SeriesCollection
anySeries.ColorIndex = colorsArray(anySeries.Index)
Next

(I'm not even sure there's such an object as anySeries.Index - but there
should be something in there that equates to it). Or even something like:
seriesCount=0
For each anySeries in ActiveChart.SeriesCollection
seriesCount=seriesCount + 1
ActiveChart.SeriesCollection(seriesCount).ColorIndex = _
colorsArray(seriesCount)
Next

 

[Gord Dibben]

If just for one workbook, you could modify the default chart colors in
Tools>Options>Color under Chart Fills and Chart Lines.


Gord Dibben MS Excel MVP

 

[Leonard Lan]

Great thanks, Gord and JLatham. I think both of your methods will help. And
through recording Macro, I found the macro to set the default colors for
chart lines is as follows,

ActiveWorkbook.Colors(25) = RGB(0, 0, 0)
ActiveWorkbook.Colors(26) = RGB(0, 0, 255)
ActiveWorkbook.Colors(27) = RGB(255, 0, 0)
...

thanks again for both of your kind help.

Regards,

Leonard