exit for loop
 

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

exit for loop
 

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

exit for loop
 

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