Hi everyone

Please excuse the terminology here. Long-time Excel user, but recent VBA fumbler!

I’ve got a PivotChart with its PivotTable underneath on a worksheet called “ChartAll”. I have a series of Slicers which all work beautifully to manipulate what’s shown on the chart (a bunch of chemical data and limits). I’ve recently contrived some VBA code (using Select/Case) to format each chemical and its limits by colour/style etc. (or to completely hide some). This, too, is working well.

One item in the chart called “NFNS” I have showing as a vertical bar, from 0 to the max of the y-axis.

I’ve recently added in two new data, “NFNSpre” and “NFNSpost” which are 1 year before and one year after the “NFNS” event/s.

I’ve got these two series showing as little markers on the x-axis. However, when there are several NFNS events, it can get a little confusing working out which pre-marker belongs to which post-marker. I’d like to be able to colour/format each pair the same, then move on to the next pair (if indeed there is another pair).

My issue is this: During the main loop (triggered when the pivot is refreshed), I think I need a loop within the Case “NFNSpre” and “NFNSpost” section to count how many there are, but I just can’t find how to extract this information. The closest I’ve come so far is:

Formula: copy to clipboard 
Please Login or Register  to view this content.


which gives me a count of the number of date categories in the pivot table. This is close, but I need to know (I think!) how many pre- and post- items are within the values. So, there might be 20 dates and 20 values for “Ammonia”, but there might only be 2 “NFNS” events, meaning that there’ll also be two pairs of pre- and post- events (NFNS 1 (event), NFNS1pre (event-1yr), NFNS1post(event+1yr), NFNS2 (event), NFNS2pre(event-1yr), NFNS2post(event+1yr)). Presumably I need to know this so that as the broad loop is working its way through each chemical, I can start a secondary loop within just these two new series to colour them the same.

Every web search I now do is just full of purple links indicating that I’m clearly barking up the wrong tree and don't know what I'm searching for!

Any help very much appreciated.

Jonathan