Charts (Excel 2000)

Topic

New Reply

Hallo
I want to know if there is a way to change the colour of bars depending on a selection for example Current or Future. I attach a sample for reference.
Any help would be appreciated.

Regards

Kobus

Reply | Quote

Replies

You could run this macro:

Sub ColorChart()
Dim sht As Worksheet
Dim cht As Chart
Dim ser As Series
Dim i As Integer
Dim n As Integer
Set sht = ActiveWorkbook.Worksheets(“Program”)
n = sht.Range(“H65536”).End(xlUp).Row
Set cht = ActiveWorkbook.Charts(“Chart1”)
Set ser = cht.SeriesCollection(2)
For i = 1 To ser.Points.Count
Select Case sht.Range(“H” & (i + 1))
Case “Current”
ser.Points(i).Interior.Color = vbRed
Case “Future”
ser.Points(i).Interior.Color = vbGreen
End Select
Next i
Set ser = Nothing
Set cht = Nothing
Set sht = Nothing
End Sub

It wil have to be run again if the data in column H change.

Reply | Quote

Hans

Thanks again to the genius.

Regards

Kobus

Reply | Quote

Here is a non-macro method (needs 2 new data columns)

Steve

Reply | Quote

Steve

Thank you also, it always amazes me how easy it seems when you guys give a solution

Reply | Quote

The non-macro solution can be expanded to other “types” (just add more columns) and is also “live” and will change as the type changes, automatically.

You can even add the legends for all the different types…

Steve

Reply | Quote