Setting Series Colours the same accross charts

[HelpNeeded]

I have several charts with around 20 different series in each. I would like to standardise the colour of each series based on the series title.

Here's my thinking in terms of the best way to go about it:

1) Based on a default chart (lets call it chart 116) for each series name, get the colour each series is set to in the chart
2) Apply the same series colour to series with the same name in the other charts

So with my default chart - chart 116.
I know how to get the series name for once series and and the colour allocated based on the macro below.


Sub test()
Dim x As String
ActiveSheet.ChartObjects("Chart 116").Activate
x = ActiveChart.SeriesCollection(7).Name
' z = ActiveChart.SeriesCollection(7).Border.ColorIndex

End Sub


Now I need to do the same for each series in the chart, and then stores the mappings somewhere, and then apply to all other charts in the sheet. Is this easy to do?

Of course, I could do this manually but I am colour blind so looking for a automated solution.

Hope someone can help

Thanks,

HN

[Bob Phillips]

What type of chart? What Excel version?

[HelpNeeded]

I'm currently using excel 2003 and the series are lines with no markers - i.e Line charts

[Bob Phillips]

Sub Test()
Const BASE_CHART As String = "Chart 116"
Dim x As String
Dim ch As ChartObject
Dim vecColours As Variant
Dim i As Long

With ActiveSheet

With .ChartObjects(BASE_CHART).Chart

ReDim vecColours(1 To .SeriesCollection.Count)
For i = 1 To .SeriesCollection.Count

vecColours(i) = .SeriesCollection(i).Border.Color
Next i
End With

For Each ch In .ChartObjects

If ch.Name <> BASE_CHART Then

For i = 1 To ch.Chart.SeriesCollection.Count

ch.Chart.SeriesCollection(i).Border.Color = vecColours(i)
Next i
End If
Next ch
End With
End Sub

[HelpNeeded]

Hi XLD,

Thanks for the code

I've tested the macro and whilst it does change the colour of series, it doesn't seem standardise the colours based on the series title but the ordering of the series.

Is it possible to include this additional criteria so that determines the colour of the series by matching up series names?

Thanks,

HN

[Bob Phillips]

Is this what you want?



Public Sub Test()
Const BASE_CHART As String = "Chart 116"
Dim x As String
Dim ch As ChartObject
Dim vecColours As Variant
Dim i As Long, j As Long

With ActiveSheet

With .ChartObjects(BASE_CHART).Chart

ReDim vecColours(1 To 2, 1 To .SeriesCollection.Count)
For i = 1 To .SeriesCollection.Count

vecColours(1, i) = .SeriesCollection(i).Border.Color
vecColours(2, i) = .SeriesCollection(i).Name
Next i
End With

For Each ch In .ChartObjects

If ch.Name <> BASE_CHART Then

For i = 1 To ch.Chart.SeriesCollection.Count

For j = 1 To UBound(vecColours, 2)

If vecColours(2, j) = ch.Chart.SeriesCollection(i).Name Then

ch.Chart.SeriesCollection(i).Border.Color = vecColours(1, i)
Exit For
End If
Next j
Next i
End If
Next ch
End With
End Sub

[Bob Phillips]

BTW, whereabouts in the UK is Jamtland :)

[HelpNeeded]

Hello XLD,

Jamtland is in London! It's a nickname!

Yes the code is exactly what I was looking for and although when I tested it out on a simple example it works perfectly, but when applying it to my orginal data set it does not work across all the charts in the same way and I'm struggling to work out why.

For illustration I'm attaching an example. The base chart is the default chart 116. The macro seems to work fine on chart 1 but not on chart 2. For example, series X2 in the base chart is red and similary in chart1 but the macro applies the colour light blue to chart 3 even though the series name is the same accross all three charts.

One explanation could be is because the number of series in this chart is different (smaller) than the others and this causing the macro not to work properly.

Does the macro ignore any series in the base chart not present in the other charts or vice versa? Maybe some kind of resume next code where there is not a match needs to be included in the macro?

Or does excel generate different colour identifier numbers based on the number of series included in the chart??

HN

[Bob Phillips]

I coded it so that it was not dependent upon any correlation in the charts, but there was an error in the code




Public Sub Chart_test2()
Const BASE_CHART As String = "Chart 116"
Dim x As String
Dim ch As ChartObject
Dim vecColours As Variant
Dim i As Long, j As Long

With ActiveSheet

With .ChartObjects(BASE_CHART).Chart
ReDim vecColours(1 To 2, 1 To .SeriesCollection.Count)
For i = 1 To .SeriesCollection.Count
vecColours(1, i) = .SeriesCollection(i).Border.Color
vecColours(2, i) = .SeriesCollection(i).Name
Next i
End With

For Each ch In .ChartObjects
If ch.Name <> BASE_CHART Then
For i = 1 To ch.Chart.SeriesCollection.Count
Debug.Assert ch.Chart.SeriesCollection(i).Name <> "X2"
For j = 1 To UBound(vecColours, 2)
If vecColours(2, j) = ch.Chart.SeriesCollection(i).Name Then
ch.Chart.SeriesCollection(i).Border.Color = vecColours(1, j)
Exit For
End If
Next j
Next i
End If
Next ch
End With
End Sub

[HelpNeeded]

This works perfectly.

Thanks.