For / For Each loops, If, With blocks

1. Practice sheet "Data"

2. For … Next — counting loop

Dim i As Long
For i = 2 To lastRow
    ws.Cells(i, 5).Value = ws.Cells(i, 2).Value * ws.Cells(i, 3).Value
Next i

i takes values 2, 3, 4 … lastRow. Inside, Cells(i, …) points at the current row.

Step changes the jump: For i = 2 To 100 Step 2 (every other row), For i = lastRow To 2 Step -1 (backwards).

3. If … ElseIf … Else

If ws.Cells(i, 2).Value = 0 Then
    ws.Cells(i, 4).Value = "Out of stock"
ElseIf ws.Cells(i, 2).Value < 5 Then
    ws.Cells(i, 4).Value = "Reorder"
Else
    ws.Cells(i, 4).Value = "OK"
End If

Combine conditions with And, Or, Not — like Excel's AND/OR.

4. Full example

Option Explicit

Sub StockStatus()
    Dim ws As Worksheet, lastRow As Long, i As Long, qty As Long
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        qty = ws.Cells(i, 2).Value
        If qty = 0 Then
            ws.Cells(i, 4).Value = "Out of stock"
            ws.Cells(i, 4).Interior.Color = RGB(255, 199, 206)
        ElseIf qty < 5 Then
            ws.Cells(i, 4).Value = "Reorder"
            ws.Cells(i, 4).Interior.Color = RGB(255, 235, 156)
        Else
            ws.Cells(i, 4).Value = "OK"
            ws.Cells(i, 4).Interior.ColorIndex = xlNone
        End If
    Next i
End Sub

Result: OK, Reorder, Out of stock, OK.

5. For Each — loop over objects

No counter needed; loop over every cell in a range, or every sheet:

Dim c As Range
For Each c In ws.Range("B2:B" & lastRow)
    If c.Value < 5 Then c.Font.Color = vbRed
Next c

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
    Debug.Print sh.Name
Next sh

c.Offset(0, 2) reaches other columns in the same row.

6. Deleting rows — loop backwards

Deleting row 4 moves row 5 up into row 4, so a forward loop skips it. Always delete from the bottom up:

For i = lastRow To 2 Step -1
    If ws.Cells(i, 2).Value = 0 Then ws.Rows(i).Delete
Next i

7. Exit For

Stop early when you've found what you need:

For i = 2 To lastRow
    If ws.Cells(i, 1).Value = "Desk" Then
        MsgBox "Desk found in row " & i
        Exit For
    End If
Next i

8. With — stop repeating the object

With ws.Range("A1:D1")
    .Font.Bold = True
    .Font.Size = 12
    .Interior.Color = RGB(31, 78, 120)
    .Font.Color = vbWhite
    .HorizontalAlignment = xlCenter
End With

Every line starting with . refers to the With object. Shorter and slightly faster.

Common mistakes

Forward loops when deleting rows. Forgetting Next i or End If (compile error). Looping cell by cell over huge ranges — fine for a few thousand rows; for more, see the array trick in Lesson 7.