Determining the value of a point in Excel VBA

Question

I am trying to have the points in a chart change color if they are within certain value paramaters (i.e., >1 is green, <1 is red, anything else is blue). I cannot determine how to get VBA to give me the value of any given point.

In this thread, previously answered, the answer (very helpful in other ways) indicates that points(num).value will return the value at the point. However, I am getting an error message doing this, and nowhere else online or in the VBA help can I find a method that corresponds to this. Has anyone else had any success with this?

Here's the snippet of code giving me trouble:

For Count = 1 To 7
    If Worksheets("Sheet1").ChartObjects("ChartName").Chart.SeriesCollection(1).Points(Count).Value > 1 Then
    '... do stuff

Because of the way the data are stored in the dataset, it would definitely be better to get the value from the chart directly. I could figure out a workaround using the dataset itself, but I would rather avoid that.

Answer 1

Sub Tester()

Dim cht As Chart, s As Series, p As Point
Dim vals, x As Integer

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set s = cht.SeriesCollection(1)

    vals = s.Values

    For x = LBound(vals) To UBound(vals)
      If vals(x) > 10 Then
        With s.Points(x)
            .MarkerBackgroundColor = RGB(255, 0, 0)
            .MarkerForegroundColor = RGB(255, 0, 0)
        End With
      End If
    Next x

End Sub