Conditional Barchart colour - Help with code !

[Raydocks]

Hi,

I have created a chart with 2 series. One is a sales value, the other in the target. I have charted this and want each bar to change colour depending on if it is above or below target.

I have calculated the colour indexes using an if statement in cells d2-d7. I have written this code that works a treat for the 1st bar in the series but the others don't change, how do I get this to change all bar colour on recalc.

Thanks,
Ray

Private Sub Worksheet_Calculate()

' Conditional Chart Colour Macro

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = Range("D2:D7")
.Pattern = xlSolid
End With
End Sub

 

[Andrew Poulsom]

Your code is only selecting the fist point. You need to loop around the Points collection like this:

Sub Test()
    Dim Pt As Point
    Dim Rng As Range
    Dim x As Integer
    Set Rng = ActiveSheet.Range("D2:D7")
    x = 1
    For Each Pt In ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(1).Points
        Pt.Interior.ColorIndex = Range("D2:D7").Cells(x, 1)
        x = x + 1
    Next Pt
End Sub