VBA loops and conditions: For Each, Do While, and If/Select Case

Looping across worksheet rows (and bottom-up with Step -1 when deleting rows) to flag overdue records automatically.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.



The Ultimate Excel Advanced Filters In VBA [Full Training Course]
Excel For Freelancers
Open on YouTube (opens in a new tab)Almost every useful macro is some combination of “do this for each of these” and “but only if this is true.” VBA gives you a few loop styles and two ways to branch — knowing which to reach for makes code much easier to read.
For…Next
Use this when you know how many times to repeat something, usually counting up (or down) through numbers:
Dim i As Long
For i = 1 To 10
Cells(i, 1).Value = i * 2
Next i
For Each…Next
Use this when you want to act on every item in a collection — every cell in a range, every worksheet in a workbook, every open workbook — without tracking an index yourself:
Dim c As Range
For Each c In Range("B2:B50")
' c refers to one cell at a time
Next c
Do While / Do Until
Use these when you don’t know in advance how many times you’ll loop — you just want to keep going while (or until) some condition holds:
Dim i As Long
i = 1
Do While Cells(i, 1).Value <> ""
i = i + 1
Loop
MsgBox "First blank row is " & i
Do Until Cells(i, 1).Value = "" would do the same thing phrased the
other way around — pick whichever reads more naturally for the check
you’re making.
If…Then…Else
For a small number of conditions:
If c.Value > 1000 Then
c.Font.Bold = True
Else
c.Font.Bold = False
End If
Select Case
For one variable being checked against several possible ranges or
values, Select Case reads more cleanly than a chain of ElseIf:
Dim score As Integer
score = Range("C2").Value
Select Case score
Case Is >= 90
Range("D2").Value = "A"
Case 80 To 89
Range("D2").Value = "B"
Case 70 To 79
Range("D2").Value = "C"
Case Else
Range("D2").Value = "F"
End Select
Worked example: bold cells over a threshold
Combining a For Each loop with an If test — a genuinely common task,
flagging values worth a second look:
Sub BoldHighValues()
Dim c As Range
Dim threshold As Double
threshold = 1000
For Each c In Range("B2:B50")
If c.Value > threshold Then
c.Font.Bold = True
End If
Next c
End Sub
Worked example: rename every worksheet
For Each also works over worksheets, not just cells:
Sub RenameSheetsWithPrefix()
Dim ws As Worksheet
Dim i As Integer
i = 1
For Each ws In ThisWorkbook.Worksheets
ws.Name = "Region" & i
i = i + 1
Next ws
End Sub
One gotcha worth knowing before you run something like this: sheet names
are capped at 31 characters, can’t contain characters like \ / ? * [ ],
and must be unique. If your naming logic would ever produce the same
name twice — or a name that already exists elsewhere in the workbook —
this will raise a runtime error partway through, leaving some sheets
renamed and others not. Test the naming logic on a copy of the workbook
first.
Leaving a loop early
Two keywords, often confused:
Exit For/Exit Do— stop looping, carry on with the rest of the procedure.Exit Sub— abandon the procedure entirely.
Stopping at the first match is both faster and clearer than letting the loop run to the end with a flag variable:
Dim c As Range
Dim foundRow As Long
For Each c In Range("A2:A10000")
If c.Value = "INV-1042" Then
foundRow = c.Row
Exit For
End If
Next c
If foundRow > 0 Then MsgBox "Row " & foundRow Else MsgBox "Not found"
There is no Continue in VBA — no way to skip to the next iteration. Invert the
condition instead: wrap the body in If Not <skip condition> Then ... End If.
Nested loops: name the counters
Nested loops over rows and columns are routine. Use descriptive counter names
rather than i and j — mixing them up produces code that runs happily and
gives wrong answers, which is far worse than code that crashes.
Dim r As Long, col As Long
For r = 2 To 100
For col = 2 To 13
If Cells(r, col).Value < 0 Then
Cells(r, col).Interior.Color = RGB(255, 199, 206)
End If
Next col
Next r
Next col before Next r — the inner loop always closes first. Getting this
backwards is a compile error, which is the one mercy here.
Three mistakes that cause most loop bugs
1. Deleting rows forwards. When you delete row 5, everything shifts up — the old row 6 becomes row 5, and your loop moves to row 6 and skips it. With consecutive blanks you delete roughly half of them and the result looks almost right, which is how it survives testing.
Always iterate backwards when deleting:
For i = lastRow To 2 Step -1
If Cells(i, 1).Value = "" Then Rows(i).Delete
Next i
Faster still is collecting the rows with Union and deleting once — see
VBA snippets you’ll reuse constantly.
2. Touching the worksheet inside the loop. Every Cells(i, 1).Value is a
cross-boundary call into Excel. At 50,000 rows the cost dominates everything
else, and the usual complaint — “my macro takes four minutes” — is almost always
this.
Read once into an array, loop the array, write once:
Dim data As Variant
data = Range("A2:C50000").Value
For i = 1 To UBound(data, 1)
If data(i, 2) > 1000 Then data(i, 3) = "High"
Next i
Range("A2:C50000").Value = data
Same logic, typically 50–100× faster. The one thing it cannot do is set
formatting — Font.Bold and Interior.Color need the real cells — so for
formatting work, build a Union range in the loop and apply the format once at
the end.
3. A Do While whose condition never becomes false. Forget the i = i + 1
and Excel hangs with no error and no way back except Ctrl + Break (which
does not always land) or Task Manager.
While developing a Do loop, add a guard:
Dim guard As Long
Do While Cells(i, 1).Value <> ""
i = i + 1
guard = guard + 1
If guard > 100000 Then
MsgBox "Loop guard tripped - check the exit condition"
Exit Do
End If
Loop
Remember that a macro cannot be undone, so an infinite loop that was halfway
through writing cells leaves the workbook in a state Ctrl + Z will not fix.
If versus Select Case, in practice
Use If for one or two conditions, or when the conditions test different
things:
If c.Value > 1000 And c.Offset(0, 1).Value = "Open" Then
Select Case cannot express that — it tests one expression against several
possible values. When that is what you are doing, it is clearly better:
Select Case region
Case "North", "North East"
rate = 0.1
Case "South"
rate = 0.12
Case Else
rate = 0.08
End Select
Note Case "North", "North East" — several values on one line, which an
ElseIf chain has to spell out with Or. And always write a Case Else, even
if it only raises a message. Unexpected values are the normal reason a
calculation comes out wrong rather than erroring, and the silent fall-through is
what hides it.
Stepping through a loop
The fastest way to understand a loop that is not doing what you expect: put the
cursor in the procedure and press F8 to step one line at a time, watching the
worksheet update behind the VBE window. Hover over a variable to see its value,
or add it to the Watch window.
For a long loop, set a breakpoint (F9) on the line inside the If so
execution stops only when the interesting branch is taken.
VBA editor basics covers the debugging tools,
and error handling covers what to
do when a loop hits a row it cannot process — generally skip and log it rather
than stopping, so one bad row does not abandon the other 9,999.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Turn the idea into a repeatable Excel workflow you can explain, rebuild, and review later. For this article, the goal is to practice: Which loop to reach for and why, how If and Select Case differ in practice, and the three mistakes — deleting forward, looping cells one at a time, and the infinite Do While — that cause most loop bugs.
Practice workbook setup
Create a small practice workbook with one raw-data sheet, one working sheet, and one final output sheet.
Practice workflow
- Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
- Write a short note beside the result explaining what each step is doing and why it matters.
- Change one input value and confirm the output updates in the way you expected.
- Save a clean copy of the workbook before experimenting further.
Quality checks
- Inputs, calculations, and final outputs are separated clearly.
- Headings describe the data without relying on memory or hidden context.
- The final result can be understood by someone who did not build the workbook.
Common mistakes
- Mixing raw data and manual adjustments in the same cells.
- Skipping a quick review after the result looks correct.
- Building a one-off fix instead of a repeatable workflow.
Next actions
- Apply the same pattern to a real workbook with 20 to 50 rows of sample data.
- Add one note that explains when you would not use this approach.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.






