Is For Each cell In Range slow on large ranges?

It is slower than reading the range into an array first, but for a few hundred cells the difference is not noticeable. For tens of thousands of cells, see the array-based approach in this guide's Array vs Range companion post.

How do I loop through a range that isn't a simple rectangle?

For Each cell In Range still works on a non-contiguous range built with Union, or on a range returned by SpecialCells — it iterates every individual cell regardless of how the range object was assembled.

Can I skip blank cells while looping?

Yes — add an If Not IsEmpty(cell) Then check, or If cell.Value <> "" Then, at the top of the loop body before running the variance test.

Should I use cell.Value or cell.Value2 in the comparison?

Value2 is marginally faster and skips currency/date formatting overhead, which matters when looping thousands of cells. For a few hundred cells in a variance sheet, either works fine.

VBA Loop Through Range of Cells: Flag Variance Outliers

This guide is part of the FinDataPro Excel VBA Workbook Events series.

The For Each Cell Loop

Run this on a defined budget-vs-actual variance range:

Option Explicit

Sub FlagVarianceOutliers()
    Dim cell As Range
    Const Threshold As Double = 0.1 ' 10% variance tolerance

    For Each cell In ThisWorkbook.Sheets("Budget").Range("D2:D50")
        If Not IsEmpty(cell) Then
            If Abs(cell.Value) > Threshold Then
                cell.Interior.Color = RGB(255, 199, 199)
            Else
                cell.Interior.ColorIndex = xlColorIndexNone
            End If
        End If
    Next cell
End Sub

Only the D2:D50 variance column is touched — the rest of the sheet is left alone.

Download the Example Workbook — Free

The working .xlsm file with a sample Budget sheet and this variance-flagging macro already wired up — open it, inspect the code, and adapt the threshold to your own tolerance.

Where the Code Goes

This lives in a standard module and can be run manually, or triggered automatically from a Worksheet_Change event so the flagging updates live as actuals are entered, rather than requiring a manual re-run.

Finance Use Case: Targeted Variance Flags

A budget-vs-actual sheet with dozens of line items makes manual variance-scanning slow and inconsistent between reviewers. Looping a single, precisely-scoped range — the variance column, not the whole sheet — flags every line beyond tolerance in one pass, using the same threshold every time, regardless of who runs it.

Manual variance review also tends to drift over time — one reviewer flags anything over 8%, another only bothers past 15%, and a third eyeballs the numbers without a fixed threshold at all. A macro removes that inconsistency entirely: the Threshold constant is the single source of truth for what counts as an outlier, and it applies identically whether the person running the macro is the preparer, the reviewer, or a manager checking the file for the first time.

Range Loop vs Whole-Worksheet Loop

This differs from looping through worksheets in scope, not mechanism — both use For Each, but one iterates sheet objects across a workbook while this one iterates individual cells within a single defined range. Choose the range-level loop whenever the target is a specific block of cells, not an entire tab.

Common Mistakes

Looping the entire UsedRange when only one column actually needs testing, which is slower and risks flagging unrelated cells.
Forgetting to reset the fill colour on cells that fall back within tolerance, leaving stale red flags behind.
Comparing text-formatted numbers directly with Abs(), which raises a type mismatch — confirm the column is genuinely numeric first.
Hardcoding the range address instead of anchoring it to a named range, so the macro silently misses new rows appended below.

Skip the manual variance scan entirely

FinDataPro Compare Data finds every difference between two Excel sheets automatically, surfacing variances without a single line of VBA.

See Compare Data

For the workbook-wide version of this pattern, see Loop Through Worksheets, or trigger this flagging live with Worksheet_Change.

Full reference: Microsoft Learn Range object reference.

Frequently Asked Questions

Is this loop slow on large ranges?

Slower than an array-based read for tens of thousands of cells, but fine for a few hundred — see the Array vs Range companion post for the threshold.

Does this work on a non-rectangular range?

Yes — it iterates every individual cell regardless of whether the range was built with Union or SpecialCells.

Can I skip blank cells?

Yes — add an If Not IsEmpty(cell) Then check at the top of the loop body.

Should I use cell.Value or cell.Value2?

Value2 skips formatting overhead and is marginally faster on large loops, though either works for a few hundred cells.

Conclusion

A tightly-scoped For Each cell In Range loop turns manual variance-scanning into a one-click, consistent check. Anchor the range to a named range so new rows are never missed, and pair it with Worksheet_Change for live flagging. If the comparison itself is the bottleneck, see Compare Data.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.

Discussion

Comments are moderated and appear once approved.