Conditional Format Chart Data Series Points

Background: I have a Chart sheet with a combination bar/line chart on it. Data is coming from a "datasheet". There is a combo box on the chart sheet that allows you to choose a specific office whose data you want to view. Pick an office from the dropdown and the chart changes. The data is a count of closed cases by month. The data sheet shows 22 Offices with their monthly data, along with entries for each month of the Upper Control Point, Lower Control Point and Mean based on the nationwide data for that month.

The chart itself uses bars to show the office's case count per month, and I've incorporated 3 additional data series to plot the UCP, LCP and Mean as lines for each month.

I'd like to change the color of the bars to be Blue, Green or Red depending on where they fall in the 3 lines. I sort of have it working, but I'm stuck.

The code below grabs the value of each series point and compares it to the 3 control points and does change the colors. I've figured out how to use the If, next to change from one series point to the next, but cannot figure out how to loop through each of the control points. As it stands now, it looks only at the first month's control point and uses that for each successive month.

I hope this is clear and I've tried to keep it as short as possible while still providing enough information.

Sub chartbarformat()
'
' chartbarformat Macro
' Macro recorded 5/15/2008 by vacoyoungd
'
Dim x As Integer
Dim val As Variant
Dim AveragePending As Range
Dim LowerPending As Range
Dim UpperPending As Range