Excel - changing a column colour on a chart 1

[Stretchwickster]

I generate a chart (xlColumnClustered) on a fresh worksheet in Excel. I then want to check if any of Y-Axis values are over 100. If a column is over 100 then I want to change the colour of that particular column to red. I can identify which value needs to be changed using the code below but how do I get direct access to a particular column and does it have its own colour/color property?

  Dim yValues
  Dim i As Integer
  Dim counter As Integer

  yValues = ActiveChart.SeriesCollection(1).Values
  counter = 0
  For i = 1 To UBound(yValues)
    If yValues(i) > 100 Then
      counter = counter + 1
      MsgBox counter
      
    End If
  Next i

Clive

 

[xlbo]

You need to use the points collection of the seriescollection object
Got this from recording:

ActiveChart.SeriesCollection(1).Points(3).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Therefore, with your code
Dim yValues
Dim i As Integer
Dim counter As Integer

yValues = ActiveChart.SeriesCollection(1).Values
counter = 0
For i = 1 To UBound(yValues)
If yValues(i) > 100 Then
with activechart.seriescollection(1).points(i)
.interior.colorindex = 3
end with
counter = counter + 1
MsgBox counter

End If
Next i

HTH Rgds
~Geoff~

 

[Stretchwickster]

Just the job - cheers Geoff! A star for you! Clive