Hi all

I want to improve my VB colorbar sub programing to color bar chart below.
This sub has the problem of range pre-defined at A2:A9.
For Each Rng In Range("A2:A9") !!
How can I modify it so it can scan any range? Because my workbook change
often but the column name not change.
Thanks
Daniel


-------------------------------------------------------

Sub colorbarr()



Application.ScreenUpdating = False

Dim Rng As Range
Dim Cnt As Integer

Cnt = 1

For Each Rng In Range("A2:A9")
Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
If Rng.Value = "im" Then
Pts.Interior.ColorIndex = 24
ElseIf Rng.Value = "surg" Then
Pts.Interior.ColorIndex = 45
ElseIf Rng.Value = "ms" Then
Pts.Interior.ColorIndex = 19
ElseIf Rng.Value = "other" Then
Pts.Interior.ColorIndex = 35
End If
Cnt = Cnt + 1
Next Rng
End Sub