vba to get name of series collection point on chart

[dodger7]

Hi Guys

Ive got a workbook with 5 sheets, all having at least 3 charts on each sheet. Im writing a bit code to change the color of each series point, depending on the name of the point.

Each point will always end in either Q1, Q2, Q3 or Q4.
Ive pretty much got the code, except I cant get the syntax for the variable 'ptname':

For Each sh In ActiveWorkbook.Worksheets
For Each ch In c.ChartObjects
For Each sc In p.Chart.SeriesCollection
For Each pt In m.Points
 
ptname = WorksheetFunction.Index(sh.ch.sc.XValues, pt)
 
Select Case Right(ptname, 2)
 
Case Is = "Q1"
'color for Q1
 
Case Is = "Q2"
'color for Q2
 
Case Is = "Q3"
'color for Q3
 
Case Is = "Q4"
'color for Q4
 
End Select
 
 
 
Next pt
Next sc
Next ch
Next sh

Any suggestions greatly appreciated!
Thanks
Jamie

 

[Andrew Poulsom]

You still haven't told me what actual data you are charting. Without knowing that it's impossible to say why "the 1st value returns a 1 and the subsequent values return the xvalue value".

 

[JB1986]

The physical values are .fullseriescollection.xvalues = "{"At Risk","Open","Complete"}" and .fullseriescollection.values="{5,6,2}"

Hope this clarifies, thanks again.

 

[Andrew Poulsom]

When I plot that data the SERIES formula looks like this:

=SERIES(,{"At Risk","Open","Complete"},{5,6,2},1)

and running the macro results in three message boxes containing "At Risk","Open" and "Complete" in that order. That's in Excel 2010.