Changing Individual Column Colours in a barchart

[Charls]

Hi,

I need to be able to change individual column's colour in a series in bar charts, i.e.

IF Column Value > Target THEN
Column Colour = Green
ELSEIF Column Value = Target THEN
Column Colour = Orange
ELSE Column Colour = Red
END IF

I need to be able to do it for all the charts in the Workbook and for each column in each chart.

But I don't know how to do this in VB or even if it can be done.

Does anyone have any suggestions?

Regards, Charls

[SammyB]

Assuming that you only have Chart sheets, this code changes anything with a value > 12 to green. If you have charts that are embedded on Worksheets, then you must first iterate through the Worksheets collection, then for each worksheet, iterate through the ChartObjects collection. I left the elseif's and color to you. To figure out the rest, record a macro and set a column manually. Let me know if you have embedded charts. HTH --Sam

Sub Macro2()
    Dim c As Chart
    For Each c In ActiveWorkbook.Charts
        Dim s As Series
        Set s = c.SeriesCollection(1)
        For i = 1 To s.Points.Count
            If s.Values(i) > 12 Then s.Points(i).Interior.ColorIndex = 10
        Next i
    Next c
End Sub