Thanks Keith
Somtimes I have no data in my excel.
How can I modify in my code that it will 'end sub' when no data?
Thanks
Daniel

"Keith R" wrote:

Inline, below. HTH.

"Daniel" wrote in message
...
Below is color bar VBA to color bar chart. The problem is
if an empty cell exist in range (A2: A9), the program will not working
How can I exit for if an empty cell exit?

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
Else
Exit for
End If
Cnt = Cnt + 1
Next Rng
End Sub