Unable to read series point values-CodePudding

I am trying yo identify series points which are under 0 and then put the bar in RED.

The following loop should do the trick but it throw an error :

For i = 1 To cht.FullSeriesCollection(1).Points.Count
        If (cht.FullSeriesCollection(1).Values(i) < 0) Then cht.FullSeriesCollection(1).Points(i).Format.ForeColor.RGB = RGB(255, 0, 0)
    Next i

where cht is :

Set cht = Ws.ChartObjects("Chart 5").Chart

The thrown error is :

Property let procedure not defined and property get procedure did not return an object

When the execution stop, the following line of code is highlighted :

cht.FullSeriesCollection(1).Values(i)

I also tried :

cht.SeriesCollection(1).Values(i) < 0

Data Set : X axis =

18.3 
11.5
6.1 
4.2
1.8

Y axis =

-1.2
-4.8
-9.6
 17.8 
 0.9

CodePudding user response：

You're close; the issue is with .Points(i).Format.ForeColor.RGB, which should be:

.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)

For more readability, try something like the following:

Dim cht As Chart
Set cht = Ws.ChartObjects("Chart 5").Chart

Dim s As Series
Set s = cht.FullSeriesCollection(1)

Dim i As Long
For i = 1 To s.Points.Count
    If s.Values(i) < 0 Then
        s.Points(i).Format.Fill.ForeColor.RGB = vbRed
    End If
Next

Sample Chart:

Sample Chart 2: