Excel VBA Intersect: Check Whether Cell Ranges Overlap

In Excel VBA, use Application.Intersect to find the cells shared by two or more ranges on the same worksheet. It returns a Range object when they overlap and returns Nothing when they do not, so test the result with Is Nothing before using it.

What Intersect does

Application.Intersect compares ranges and produces the portion of their cells that belongs to every range supplied. For example, the intersection of A1:C5 and B3:D7 is B3:C5. If the ranges have no cells in common, there is no intersection.

This is useful when a macro needs to respond only to changes in a particular part of a sheet, check whether a selected cell belongs to a monitored area, or identify overlap between ranges before making a change. The method does not change cell values or formatting by itself. It returns a reference to the overlapping cells; your code decides what to do with that reference.

In VBA, the method is called on the Excel Application object. It is not a worksheet function to enter into a cell. Because the result is an object, assign it with the Set keyword, just as you would assign a worksheet or range to an object variable.

Syntax and worksheet requirements

The general syntax is Application.Intersect(Arg1, Arg2, ...). Each argument must be a Range object. Supply at least two ranges; additional ranges narrow the result to cells common to all of them. For instance, a cell must be in the first range, the second range, and the third range to appear in a three-range intersection.

All arguments must refer to ranges on the same worksheet. If you pass ranges from different worksheets, Excel raises a run-time error rather than returning Nothing. Qualify each range with its worksheet instead of relying on whichever sheet happens to be active. This makes the code easier to understand and avoids accidental references to the wrong sheet.

The method can work with multi-cell ranges and multi-area ranges, such as a range created with Union. The result can also contain more than one area if the common cells are separated. If your code assumes one continuous block, account for that possibility before using properties such as Row or Column as if there were only one area.

A basic VBA example

This macro compares two ranges on the worksheet named Data. With A1:C5 and B3:D7 as the inputs, the overlap is B3:C5, a block of six cells. The code checks for a missing intersection before reading the result:

Sub CheckOverlap()
    Dim ws As Worksheet
    Dim firstRange As Range
    Dim secondRange As Range
    Dim overlap As Range

    ' Keep both range references on the same worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set firstRange = ws.Range("A1:C5")
    Set secondRange = ws.Range("B3:D7")
    Set overlap = Application.Intersect(firstRange, secondRange)

    If overlap Is Nothing Then
        MsgBox "The ranges do not overlap."
    Else
        MsgBox "Overlap: " & overlap.Address(False, False)
    End If
End Sub

Address(False, False) displays the result without dollar signs, so this example reports B3:C5. If the second range were F3:G7, the result would be Nothing and the first message would appear. Use Set for the assignment to overlap; omitting it causes an object assignment error.

To run a macro, first save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a macro that changes data or formatting may require restoring the saved copy if it produces an unwanted result. In Microsoft 365 for Windows, open the VBA editor with Alt+F11; on Mac, the editor can be opened with Option+F11 or through the Developer tab, depending on the keyboard and system settings.

Using Intersect in a Worksheet_Change event

A common use is limiting a worksheet event macro to a watched input range. The event’s Target argument represents the cell or cells that were changed. Intersect lets the code determine whether any part of that changed range touches the area of interest. Put this code in the code module for the relevant worksheet, not in a standard module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watched As Range
    Dim changed As Range

    ' Monitor entries in column B, rows 2 through 100.
    Set watched = Me.Range("B2:B100")
    Set changed = Application.Intersect(Target, watched)

    If changed Is Nothing Then Exit Sub

    MsgBox changed.CountLarge & " watched cell(s) changed."
End Sub

If someone edits B8, changed refers to B8. If they paste values into B8:B10, it refers to all three cells, and CountLarge reports three. If they edit D8, there is no overlap and the event exits without doing anything. Checking the intersection matters because a paste can make Target a multi-cell range; code should not assume that every change affects exactly one cell.

This example only displays a message and does not edit the sheet, so it does not trigger itself again. If an event macro writes to cells, it may cause another change event. In that situation, use a carefully managed Application.EnableEvents setting and restore it even if an error occurs. Avoid turning events off without a reliable cleanup path, since they can otherwise remain disabled for the Excel session.

Checking several ranges

You can pass more than two ranges to Intersect when a cell must satisfy multiple conditions. For example, the intersection of a table’s data area, a particular column, and a permitted row region identifies only cells common to all three references. This can be clearer than nesting several separate membership checks.

It can also be useful with a noncontiguous selection or a range assembled with Union. Think carefully about what “overlap” means in that case: the result may consist of multiple areas. The Areas collection lets code inspect those separate blocks individually. For a quick test that only asks whether any overlap exists, checking whether the result is Nothing is usually enough.

For readable code, assign the result to a clearly named Range variable rather than calling Intersect repeatedly. That reduces duplication and makes it easier to inspect the result in the debugger. If ranges are created dynamically, confirm that each reference is valid and belongs to the intended worksheet before making the call.

Common errors and how to avoid them

Using equals instead of Is Nothing: An object result should be tested with If overlap Is Nothing Then. Do not compare it with a text value or treat it as a number.
Forgetting Set: Assigning the returned Range object requires Set overlap = Application.Intersect(...).
Mixing worksheets: Qualify every range with the same worksheet object. Intersect cannot calculate a shared cell range across different sheets.
Assuming a single-cell change: Worksheet events can receive a multi-cell Target after a paste or fill operation. Use the returned intersection as a range and account for all its cells.
Using an unqualified Range: A reference such as Range("A1:A10") depends on the active sheet. Prefer ws.Range("A1:A10") or, in a worksheet module, Me.Range("A1:A10").
Assuming no overlap means an empty Range: The no-overlap result is the special object value Nothing. It is not a zero-cell range, so test it before asking for its address or count.

If VBA stops with a run-time error at the Intersect call, first check whether the arguments belong to different worksheets. If it stops later while accessing the result, check whether the ranges did not overlap and the code used the result before testing it. These are distinct problems: invalid input references can raise an error, while a valid comparison with no shared cells returns Nothing.

VBA Intersect versus the formula intersection operator

Excel also has an intersection operator in worksheet formulas: a space placed between range references. That is separate from the VBA method. In a formula, the space asks Excel to return the common cells from the referenced ranges; in VBA, Application.Intersect returns a Range object that code can inspect or manipulate.

The formula operator can be useful for a direct worksheet calculation, but it is not the right syntax inside a VBA expression. Conversely, calling Application.Intersect is appropriate when a macro needs to branch, format cells, or respond to an event. A related feature, the implicit intersection operator @ in modern Excel formulas, is not the same thing as range intersection: it selects a single value from an array or range context rather than returning the shared cells of two ranges.

When to choose Intersect

Use Intersect when the question is about whether two or more actual cell references share cells. Typical cases include restricting event handling, checking if a selection includes a control area, validating that a proposed range is within an allowed region, or limiting a formatting routine to a target block.

For other questions, a different approach may be simpler. If you only need to compare values in two columns, use ordinary cell comparisons or lookup logic rather than treating those columns as intersecting ranges. If you want to construct a range containing cells from separate references, Union is the companion method: it combines ranges, while Intersect returns their common portion. Neither method compares the contents of cells; they operate on range references.

Before using the result to make changes, decide whether the code should process every cell in the intersection or only test whether an overlap exists. For a membership test, the Nothing check is sufficient. For processing, iterate over the returned range, and consider that it may contain multiple cells or areas. This distinction prevents a small overlap check from becoming an accidental operation on a larger-than-expected target.

Frequently Asked Questions

What does Application.Intersect return?
It returns a Range object containing the cells shared by all supplied ranges. If there are no shared cells, it returns Nothing. Store the result in a Range variable with Set.

How do I check whether two ranges overlap in VBA?
Assign Application.Intersect(range1, range2) to a Range variable, then test whether that variable Is Nothing. If it is not Nothing, the ranges overlap.

Can Intersect work across different worksheets?
No. The range arguments must all be on the same worksheet. If the references are on separate sheets, Excel raises a run-time error; compare or handle the ranges separately instead.

Can Intersect handle a pasted block of cells?
Yes. A multi-cell range can be an argument, and the returned range can include multiple cells. In a Worksheet_Change event, test the complete Target range rather than assuming only one cell changed.

What is the difference between Intersect and Union?
Intersect returns only cells common to the supplied ranges. Union combines the supplied ranges into one range reference, including their separate areas when they are not adjacent.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.