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
>Solution :
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:
