I want to create some conditional formatting in a chart e.g. the crosstab query on which the chart is based will return different numbers of fields but I want the colours to remain constant for each field. For instance the chart may return fields fld1, fld2, fld3 on the first query but only fld1 and fld3 on the next occasion. I want fld3 to always be red, additionally I have to create a legend - the built in legend does not work properly with a two axis chart, it displays the series in the order 1,3,2,4 instead of 1,2,3,4.
How can I assign a colour to a particular series. Ive tried the following
Dim cht As Object
Dim sr As Series
Set cht = Me!MyChart.Object
If cht.SeriesCollection = "fld3" Then sr.Border.ColorIndex = 3
But this produces an error 'Object doesn't support this property or method'.
I've tried using the Object Browser but I haven't found it very helpful because it seems to assume the user knows precisely what he is looking for but in my case I don't! I need to know how to test if a Series named 'fld1' exists and searching under Chart, Series, SeriesCollection or Name doesn't give me a clear enough answer. Guidance on the use of the Object Browser also appreciated.