Compare Last Points of Mulitple SeriesCollection

[shadyferret]

Hi everyone,

I've created a performance-based chart that updates every week and I need to change the plot background depending on the last point for 3 series.

Example:
Week 5
Series 1, Point 5 = 10
Series 2, Point 5 = 5
Series 3, Point 5 = 12

Based on these points, the plot area would be turned orange because Series 1 is greater than Series 2 but not greater than Series 3.


I don't control the source data for Series 2 and 3 so I'd prefer to use the series for my data to minimize how much manual work I have to do if the people who do control those series make changes (which is likely to happen in the near future and possibly again in the Fall).


I'm planning on controlling the plot area with If/Then/Else or Case loops, but I can't figure out how to reference or retrieve the data for the last point of these series. It seems that either SeriesCollection(1).Points.Value or SeriesCollection(1).Points.Count would work, except I'm not sure how to use them with a variable. I was thinking of making a For/To Loop that step backwards and exits the loop once it encounters a point with data and stores that data within a variable, but I'm lost on referencing chart data instead of cells within a range from a sheet. Any help? I'm open to different ideas of doing this besides my loop idea.

What I mainly need help on is finding the data for the last (non-empty) point and how to store it or reference it so I can continue with the rest of my loops. Then I can probably finish my code.


Thanks in advance!!