📎 This article includes 1 downloadable practice file ↓

📘 Excel VBA Course · Lesson 5 of 15 — see all lessons

These are small macros I keep coming back to. Each one teaches a technique you will reuse elsewhere.

1. Mod: every Nth row

Mod returns the remainder of a division — 7 Mod 3 is 1. It is perfect for “every other row” or “every 5th item”.

Sub ZebraStripes()
    Dim r As Long
    For r = 2 To 200
        If r Mod 2 = 0 Then Rows(r).Interior.Color = RGB(245, 245, 245)
    Next r
End Sub

2. Second highest value

Dim second As Double
second = Application.WorksheetFunction.Large(Range("B2:B100"), 2)
MsgBox "Second highest: " & second

Any worksheet function is available through Application.WorksheetFunction — no need to write your own loop.

3. Sum by cell colour

Excel formulas cannot read fill colours, but a custom function can:

Function SumByColour(data As Range, sample As Range) As Double
    Dim c As Range
    For Each c In data
        If c.Interior.Color = sample.Interior.Color Then
            If IsNumeric(c.Value) Then SumByColour = SumByColour + c.Value
        End If
    Next c
End Function

Use it in a cell: =SumByColour(B2:B50, D1), where D1 has the colour you want. Note: changing a colour does not trigger recalculation — press Ctrl+Alt+F9. (Colours set by conditional formatting are not seen by Interior.Color.)

4. Delete blank rows (safely)

Sub DeleteBlankRows()
    Dim r As Long, lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lastRow To 2 Step -1          ' go BACKWARDS
        If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
End Sub

5. Highlight duplicates in a column

Sub HighlightDuplicates()
    Dim c As Range, seen As Object
    Set seen = CreateObject("Scripting.Dictionary")
    For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        If seen.Exists(c.Value) Then
            c.Interior.Color = vbYellow
        Else
            seen.Add c.Value, True
        End If
    Next c
End Sub

The Dictionary object remembers what has been seen — a technique worth learning on its own; it is far faster than a nested loop.

Try it yourself: step by step

1. Insert three empty rows in the middle of the Data sheet.
2. Run L05_DeleteBlankRows — the gaps disappear because the loop runs backwards.
3. Change the loop to run forwards (For r = 2 To lastRow) on a copy and see rows being skipped.
4. Copy the SumByColour function from the article into the module, colour a few Amount cells yellow and use =SumByColour(H2:H61, J1).
5. Run Bonus_CountByRegion to see a Dictionary count invoices per region.