macro for fixed color lines in line chart based on series name

[sm]

i plot line charts for the growth trends of top product brands..i want to
have the same color of the line for a particular brand in all the charts i
create...so i need a macro to have a fixed color for the series based on the
series name, so that i do not have to format each and every line of every
chart, which is very time consuming as i have to do some 50 charts..

i am using excel 2003

 

[Andy Pope]

Hi,

Something like this maybe, test on a copy first.

Sub X()
Dim objCht As ChartObject
Dim colData As Collection
Dim lngColorIndex As Long
Dim serX As Series

Set colData = New Collection
' colour value and unique series name
colData.Add 3, "A"
colData.Add 12, "B"
colData.Add 34, "C"
colData.Add 45, "D"

On Error Resume Next

For Each objCht In ActiveSheet.ChartObjects
For Each serX In objCht.Chart.SeriesCollection
lngColorIndex = -1
lngColorIndex = colData(UCase(serX.Name))
If lngColorIndex >= 0 Then
serX.Border.ColorIndex = lngColorIndex
End If
Next
Next

End Sub

Cheers
Andy