How to change the color of all series in an excel chart in one go.

[Guest]

In Excel 2000
I have a chart (XY) with a large number of series. Excel assigned different
colours to each series. I would like to have the same color for all series
and then change a few to highlight them. Do I have to change them all one by
one or can I have excel make a chart where the series all have the same color
(preferably chosen by me)?

 

[Guest]

Marielle,

Perhaps you can use VBA to automate the line coloring and then use a custom
chart as a default for your new charts . . .

This macro will return the series color index for series number 1. If you
don’t know the color index for the series that you want, try a color and then
run this. You can then apply the number to the other macros below.

If you want the marker colors, take out the apostrophe in front of those
lines of code.

Sub SeriesColorIndex()
‘get color indices
Dim A As Integer 'Line Color Index
Dim B As Integer 'Marker Background Color Index
Dim C As Integer 'Marker Foreground Color Index
Set Cht = ActiveChart
Set Srs1 = Cht.SeriesCollection(1)
A = Srs1.Border.ColorIndex
‘B = Srs1.MarkerBackgroundColorIndex
‘C = Srs1.MarkerForegroundColorIndex
MsgBox A
MsgBox B
MsgBox C
End Sub

This macro colors all of the series the same color:

Sub ColorAllSeries()
Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(2)
For Each Sr In Srs
Sr.Border.ColorIndex = 6
‘Sr.MarkerBackgroundColorIndex = 6
‘Sr.MarkerForegroundColorIndex = 6
Next Sr
End Sub

This macro colors the series number 2:

Sub ColorSingleSeries()
Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(2)
Srs.Border.ColorIndex = 6
‘Srs.MarkerBackgroundColorIndex = 6
‘Srs.MarkerForegroundColorIndex = 6
End Sub

To create a custom chart that you base all of your other on (so you don’t
have to format each line in each chart that you create), click on your master
chart and go through the following:

Chart -> Chart Type -> Custom Types Tab
Go to Select From -> User Defined
Hit “Addâ€
Give the chart a name and description
Save the chart and use it as the default for your others