Use a Variable to select a Data Item in a chart | Excel forum

I am writing a macro to produce a set of reports for customers. The idea is that there is a standard template with a series of lookups to show the relevant information for each customer, which gets copied and pasted into a new excel sheet per customer to be sent out.

One of the things I am trying to do is show the relative position on a bar chart of the customer against all others in a group by highlighting the particular customer in a different colour. I have a lookup that gets the position in the chart based on the customer ID, I just can't figure out how to use this position in the code. My code is as follows:

Sub testchangecolour()
'
' testchangecolour Macro
'

'
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(12).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Solid
End With
End Sub


In the line ActiveChart.SeriesCollection(1).Points(12).Select, I want to make Point(12) a reference to the cell that holds the position so that it will change depending on the customer selected. Adding the cell reference didn't work. How can I do this?