Wauvel

Excel like a finance pro.

← All VBA snippets

Build a range out of pieces, or find the overlap

Union formats scattered cells in one call. Intersect is how an event knows the edit mattered.

1What it does

Two range operations that make loops unnecessary. Union joins separate ranges into one — so instead of formatting forty flagged cells one at a time, you collect them as you find them and format the whole set at the end, which is dramatically faster. Intersect returns the overlap of two ranges, or Nothing if there isn't one. That's the question every change-event macro needs to ask: did the cell someone just edited fall inside the input block I care about? Without it, the event fires its logic on every keystroke anywhere on the sheet.

2The code

Sub FlagOverdueRows()
    Dim ws As Worksheet, r As Long, lastRow As Long
    Dim flagged As Range

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For r = 2 To lastRow
        If ws.Cells(r, 4).Value > 60 Then          ' days overdue
            ' Union can't start from Nothing, so seed it the first time.
            If flagged Is Nothing Then
                Set flagged = ws.Range(ws.Cells(r, 1), ws.Cells(r, 4))
            Else
                Set flagged = Union(flagged, ws.Range(ws.Cells(r, 1), ws.Cells(r, 4)))
            End If
        End If
    Next r

    ' ONE formatting call for every flagged row, instead of one per row.
    If Not flagged Is Nothing Then flagged.Interior.Color = RGB(255, 226, 220)
End Sub

' In the worksheet's code module: react only to edits inside the input block.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("B4:B12")) Is Nothing Then Exit Sub
    Me.Range("E2").Value = "Inputs changed " & Format(Now, "hh:mm")
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Collect every row meeting a condition and format them in one call.
  • Make a change event react only to edits in its input range.
  • Test whether a selection overlaps a protected area before acting.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Acme12 days
Globex71 days
Initech38 days
Soylent94 days
Umbrella5 days
Hooli66 days

Both buttons highlight the same rows. What differs is how many times Excel has to redraw.

5Pitfalls

Union(Nothing, rng) raises an error — Union can't start from an empty range.

Fix: Seed it on the first match, then Union after that, as above.

A change event runs its whole routine on every edit anywhere, slowing typing to a crawl.

Fix: Exit at the top when Intersect(Target, inputs) Is Nothing.

Union across different sheets raises error 1004.

Fix: Both ranges must be on the same worksheet.

Intersect is checked but Target is several cells, and the code assumes one.

Fix: Loop the cells of Intersect(Target, inputs) rather than reading Target.Value directly.

6No-code alternatives

  • AutoFilter then SpecialCells(xlCellTypeVisible) — Another way to act on a subset of rows in one call, when the condition fits a filter. See the autofilter snippet.
  • Conditional formatting — For highlighting that should stay current as data changes, a rule beats a macro that has to be re-run.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.