Do....Loop Error

Sub color()
Dim x As Long


Range("A15").Select

Do
x = ActiveCell.Value
If x = "" Then Exit Do
If x >= 10 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
If x < 10 Then
With Selction.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveCell.Offset(0, 1).Select
Loop

End Sub


How come when I compile and debug I get a Loop without Do?

It is because you have not given your loop a condition on when to end; therefore it will run indefinetely.

Sub color()
Dim x As Long


Range("A15").Select

Do until variable = amount ' You need a condition either here on the DO or at the end on the LOOP
x = ActiveCell.Value
If x = "" Then Exit Do
If x >= 10 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
If x < 10 Then
With Selction.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveCell.Offset(0, 1).Select
Loop until variable = amount ' You need a condition either here on the DO or at the end on the LOOP


End Sub

Unless you are planning to expand this procedure to multiple cells, I do not understand why you are using a loop for this? Also I would probably use a for next instead of loop.

You also may want to read this topic Active Cell Movement (http://www.xtremevbtalk.com/showthread.php?t=295188) as MPi and Colin_L provided me with some very excellent examples and instructions on how to properly move around cells and how not to use activecell, select and activate.

I put in a variable, but it still didn't work......I've had this happen before, its usually something simple......i think..

The reason is because you are missing an "end if". Because you havent closed the if statement VB has seen the "loop" command and realized that there isnt a "Do" command inside the if statement.

As per the Standards and Practices Tutorial (http://www.xtremevbtalk.com/showthread.php?threadid=68153) you should have an end if for every if statement including your first one.

Your code should look like this

Sub color()
Dim x As Long


Range("A15").Select

Do
x = ActiveCell.Value
If x = "" Then
Exit Do
end if

If x >= 10 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
end if

If x < 10 Then
With Selction.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
end if

ActiveCell.Offset(0, 1).Select
Loop

End Sub


Because of the checks you are doing you can use elseif to check if x is less than 10. This also means you dont need "end if" after the if statement above because an elseif will be part of the same if statement.

Since your first if statement checks if x is bigger than or equal to 10 then the only other possibility is that it is less than 10 so instead of elseif you can simply put "else".

That section of code would nor look like this

If x >= 10 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
else
With Selction.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
end if

i knew it was something easy.....

I fixed that but now whenever the program gets to the
"if x = "" " part, I get a type mismatch error......anyone know what that means?

Yes. Looking at your code you have declared x as a long (a number). That if statement checks if it is equal to an empty string. Longs and strings are different types (type mismatch). You also copy the value from ActiveCell.Value into X which could also be a string value depending on the contents of your cells.

What should it be checking for? An empty cell or a cell with the value of 0? Im assuming you want to check for a blank cell in which case i would alter the code to look like this


If ActiveCell.Value = "" Then
Exit Do
end if
x = ActiveCell.Value


The idea is that you check if the active cell is empty. If not then copy its value into x. You can still get errors though because any text in a cell means the cell is not blank but its still a string and cannot be copied to x. It depends how you want to handle this. You could exit the loop if the activecell.value is not numberic or you could just accept any text value as whatever number VB can make out of it


'Check that it is numeric
if not isnumeric(activecell.value) then
exit do
end if

'Or try to make a number out of the string (returns 0 if a number cant be found)
x=val(activecell.value)

For the End If missing, I would say this can happen often if you don't use a strict method of coding which may not be your case...

What I do is whenever I use a For, an If, a Do, a Select Case... I always put the end of the statement (Next, End If, Loop, End Select...) right away before inserting any code. This way you make sure you don't get that kind of error.

Of course, when modifying the code it may happen that I forget a part but it sure helps to use a good method of working...

That was my 2 cents (or pennies ?)... :)