Do Loop
 

Could anyone please tell why the funnel is kept running without stopping even
after the expected task is completed.

Sub Macro()
Do Until blanks
For Each cell In Range("A1:A10")
Select Case cell.Value
Case "W"
cell.Offset(0, 1).Interior.ColorIndex = 1
Case "Q"
cell.Offset(0, 1).Interior.ColorIndex = 7
Case Else
cell.Offset(0, 1).Interior.ColorIndex = 9
End Select
Next
Loop
End Sub

Thank you

Do Loop
 

On 3 Aug, 14:04, AlanW wrote:
Could anyone please tell why the funnel is kept running without stopping even
after the expected task is completed.

Sub Macro()
Do Until blanks
For Each cell In Range("A1:A10")
Select Case cell.Value
Case "W"
cell.Offset(0, 1).Interior.ColorIndex = 1
Case "Q"
cell.Offset(0, 1).Interior.ColorIndex = 7
Case Else
cell.Offset(0, 1).Interior.ColorIndex = 9
End Select
Next
Loop
End Sub

Thank you

You have told it to run until an undeclared variable called Blanks is
true, which is never referred to again!

Do Loop
 

Alan,

You have a For Loop inside a Do Loop. Your For Loop will go through all
cells in A1:A10 and terminate when all cells have been processed. Your Do
Loop however will not terminate. It will terminate when the variable blanks
evaluates to true. You don't seem to really need the Do Loop in your code.
If your intent is to not go through the For loop if there are blanks in
A1:A10, then, you can do it with an If statement like this:

If Application.WorksheetFunction.CountA(Range("A1:A10 ")) = 10 Then
For Each cell In Range("A1:A10")
Select Case cell.Value
Case "W"
cell.Offset(0, 1).Interior.ColorIndex = 1
Case "Q"
cell.Offset(0, 1).Interior.ColorIndex = 7
Case Else
cell.Offset(0, 1).Interior.ColorIndex = 9
End Select
Next
End If



--
Hope that helps.

Vergel Adriano


"AlanW" wrote:

Could anyone please tell why the funnel is kept running without stopping even
after the expected task is completed.

Sub Macro()
Do Until blanks
For Each cell In Range("A1:A10")
Select Case cell.Value
Case "W"
cell.Offset(0, 1).Interior.ColorIndex = 1
Case "Q"
cell.Offset(0, 1).Interior.ColorIndex = 7
Case Else
cell.Offset(0, 1).Interior.ColorIndex = 9
End Select
Next
Loop
End Sub

Thank you

Do Loop
 

If you only need the code to look at A1:A10, you should take out the
'Do Until blanks' and 'Loop' code ... worked just fine for me without
those lines...

ray

Do Loop
 

Because you have not defined blanks
You don't even need it if you want to do it just for A1 to A10
Use this:

Sub Macro()
For Each cell In Range("A1:A10")
Select Case cell.Value
Case "W"
cell.Offset(0, 1).Interior.ColorIndex = 1
Case "Q"
cell.Offset(0, 1).Interior.ColorIndex = 7
Case Else
cell.Offset(0, 1).Interior.ColorIndex = 9
End Select
Next
End Sub


"AlanW" wrote:

Could anyone please tell why the funnel is kept running without stopping even
after the expected task is completed.

Sub Macro()
Do Until blanks
For Each cell In Range("A1:A10")
Select Case cell.Value
Case "W"
cell.Offset(0, 1).Interior.ColorIndex = 1
Case "Q"
cell.Offset(0, 1).Interior.ColorIndex = 7
Case Else
cell.Offset(0, 1).Interior.ColorIndex = 9
End Select
Next
Loop
End Sub

Thank you

Do Loop
 

The do until "blanks" does nothing and the do/loop is doing nothing. All you
need is the for/next loop

Sub DocolorMacro()
For Each c In Range("A1:A10")
Select Case cell.Value
Case "W":x=1
Case "Q":x=7
Case Else:x=9
End Select
c.Offset(,1).Interior.ColorIndex = x
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AlanW" wrote in message
...
Could anyone please tell why the funnel is kept running without stopping
even
after the expected task is completed.

Sub Macro()
Do Until blanks
For Each cell In Range("A1:A10")
Select Case cell.Value
Case "W"
cell.Offset(0, 1).Interior.ColorIndex = 1
Case "Q"
cell.Offset(0, 1).Interior.ColorIndex = 7
Case Else
cell.Offset(0, 1).Interior.ColorIndex = 9
End Select
Next
Loop
End Sub

Thank you