Formatting chart with variable series

[Mike Collard]

I have four column type charts based on queries that
return a variable number of series in each chart depending
on the data. So chart one may have 3 series, chart 2 may
have 4 series etc. MS Graph allocates the colour of the
series dynamically which is OK if all four charts happen
to have the same series. So if Brand A, Brand B, Brand C
and Brand D are present in each chart then Brand A will
say be red in all four charts, Brand B will be green in
all charts etc. But, if say Brand A is not present in one
chart then the colour sequence will differ and the brand
colours will be inconsistent between charts.

If a series is present in a chart then I want it coloured
the same as it is in any other chart i.e. I want all Brand
A's to be red, all Brand B's to be green etc. regardless
of how many series are present.

I have tried to adapt an Excel macro but get an error
Object doesn't support this property in relation to the
strCase statement - see below:

Dim iSrsCt As Integer, COUNTER As Integer, strCase As
String

iSrsCt = Me!
DERVGraph.Object.Application.Chart.SeriesCollection.Count

For COUNTER = 1 To iSrsCt

strCase = Me!
MyGraph.Object.Application.Chart.SeriesCollection
(COUNTER).Name

Select Case strCase

Case "BrandA"

Me!MyGraph.Object.Application.Chart.SeriesCollection
(COUNTER).Name.Interior.ColorIndex = 2

Case "BrandB"

Me!MyGraph.Object.Application.Chart.SeriesCollection
(COUNTER).Name.Interior.ColorIndex = 4

Case "BrandC"

Me!MyGraph.Object.Application.Chart.SeriesCollection
(COUNTER).Name.Interior.ColorIndex = 6

Case "BrandD"

Me!MyGraph.Object.Application.Chart.SeriesCollection
(COUNTER).Name.Interior.ColorIndex = 8
End Select
Next

Me!MyGraph.Object.Application.Chart.Deselect


Any help appreciated.

Thanks

Mike Collard