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

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.