VBA Range.Intersect: Find Overlapping Cells

In VBA, use Application.Intersect(range1, range2) to find the cells shared by two or more ranges. It returns a Range object for the overlap, or Nothing when the ranges do not overlap; the ranges must be on the same worksheet.

What Application.Intersect Does

Application.Intersect compares the addresses of ranges, not the values stored in their cells. For example, if one range is A1:C5 and another is B3:D7, the cells they share are B3:C5. The result is a Range object representing those cells, so your code can inspect them, change them, or use their properties.

This distinction is useful when you want to know whether two areas of a worksheet physically overlap. It does not tell you whether two cells contain the same text or number. To compare cell contents, read or compare their Value properties instead.

If there are no shared cells, the method returns the VBA object value Nothing. That is not the same as an empty string, zero, or an empty cell. Check for it with If result Is Nothing Then. If there is an overlap, the returned range may include blank cells; the method is concerned only with cell positions.

Syntax and Basic Usage

The method accepts two or more Range arguments. A typical call has this form:

Set overlap = Application.Intersect(firstRange, secondRange)

Because the result is an object, use Set when assigning it to a Range variable. The key steps are to declare the variables, establish the ranges, call the method, and test whether the returned object is Nothing before using it.

Here is a complete example. It checks two ranges on a worksheet named Sheet1 and reports their shared address:

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

    ' Use a specific worksheet rather than the active sheet.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    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 "Shared cells: " & overlap.Address(False, False)
    End If
End Sub

For these example addresses, the message identifies B3:C5. The two ranges cross over three columns and three rows, giving nine shared cells. The Address(False, False) arguments show a relative-style address without dollar signs; omit them if you prefer the default absolute-style address, such as $B$3:$C$5.

Qualify Ranges to Avoid Active-Sheet Bugs

Unqualified references such as Range("A1:C5") are resolved against the active worksheet. That can cause a macro to inspect a different sheet than intended if the user changes sheets or another part of the code activates a different tab. Assign a worksheet object and use ws.Range(...) for every range in the comparison.

For example, ThisWorkbook.Worksheets("Sheet1") refers to the workbook containing the VBA code. If the macro is stored in PERSONAL.XLSB or an add-in and should act on the workbook currently in use, you may instead need to identify that workbook explicitly, such as through an already assigned Workbook variable. Do not rely on whichever workbook happens to be active unless that is deliberately the macro’s design.

Both arguments to Intersect must refer to the same worksheet. If they refer to different sheets, Excel raises an error rather than treating them as a non-overlap. Qualifying both ranges with the same worksheet object helps prevent this problem.

Use Intersect in a Worksheet Change Event

A common application is checking whether a user edit touched a watched area. The Worksheet_Change event receives a Target range, which can cover one cell or many cells—for example, when someone pastes a block. Intersecting Target with a monitored range lets the procedure exit when the edit happened elsewhere.

Place this event procedure in the code module for the worksheet being monitored, not in a standard module. In the VBA editor, select that worksheet in the Project Explorer and add the procedure there.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchedRange As Range
    Dim changedCells As Range

    ' Monitor entries in B2:B100 on this worksheet.
    Set watchedRange = Me.Range("B2:B100")
    Set changedCells = Application.Intersect(Target, watchedRange)

    If changedCells Is Nothing Then Exit Sub

    MsgBox "A watched cell changed: " & _
        changedCells.Address(False, False)
End Sub

Here, Me means the worksheet whose event code is running, so the monitored range is unambiguous. If a paste changes both watched and unwatched cells, changedCells contains the part of Target that intersects B2:B100. This example only displays a message; real event procedures often use the returned range to validate input or update related cells. If an event procedure itself changes cells, consider whether that edit could trigger another event, and manage event handling carefully to avoid repeated execution.

More Than Two Ranges and Multi-Area Ranges

You can test several ranges in one call. For example, intersecting three ranges returns the cells common to all three—not every cell that appears in any one of them. This is a useful way to check whether a target is simultaneously inside several boundaries. Every argument still needs to refer to the same worksheet.

Ranges can also be non-contiguous, such as A1:A5 together with C1:C5. These are often called multi-area ranges. An intersection can itself consist of multiple areas, so do not assume every result is a single rectangular block. You can examine its Areas collection when your code needs to process each separate section.

For instance, a returned range with more than one area can be handled with a loop over overlap.Areas. Each item in that collection is a Range object. This matters when applying operations that expect a single contiguous region or when reporting addresses: a multi-area address can contain several comma-separated sections.

Common Errors and Misunderstandings

Using the wrong empty check: Test an object with Is Nothing, as in If overlap Is Nothing Then. Do not compare a Range object to an empty string or zero.
Omitting Set: Assign the returned Range object with Set overlap = .... Without Set, VBA does not assign the object reference as intended.
Mixing worksheets: An intersection across different worksheets causes an error. Confirm the sheet qualification of every argument.
Assuming a value match: Intersect checks shared addresses, not equal cell contents. Two cells containing the same number do not intersect unless they occupy the same cell location.
Assuming no result means a blank cell: Nothing means the ranges share no cells. A valid intersection can include cells that happen to be blank.
Using unqualified Range references: A reference may follow the active worksheet and produce a different result than expected. Qualify it with a worksheet variable or, in sheet event code, Me.

When debugging, inspect the addresses of the input ranges and confirm they belong to the intended sheet. A range’s Address property is handy for this. If the procedure errors before reaching the Nothing check, verify that the arguments are valid Range objects on the same worksheet.

Intersect Compared with Related VBA Approaches

Use Application.Intersect when the question is “Which cells are common to these ranges?” It is concise and returns the overlap itself, which makes subsequent work straightforward. A Boolean test can then branch on whether the result is Nothing.

Use Application.Union when the goal is the opposite kind of operation: combining ranges into one range reference, rather than finding shared cells. Union does not identify the overlap. Likewise, comparing values with a loop or a worksheet formula is more appropriate when the goal is to find matching contents, not shared cell addresses.

You can also test whether a particular cell lies within a larger range by intersecting that cell with the larger range. For example, if cellToCheck and allowedRange are Range variables on the same sheet, a non-Nothing result means the cell is within the allowed area. This is often clearer than manually comparing row and column numbers, especially when the permitted region is irregular.

Running the VBA Code

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, use Option+F11; depending on the keyboard, you may also need Fn. You can insert a standard module through Insert > Module for a regular macro, while a worksheet event procedure belongs in the relevant worksheet’s code module. Save the file in a macro-enabled format such as .xlsm if it needs to retain VBA.

Before running a macro, save a copy of the workbook. Excel macros cannot be undone with Ctrl+Z, even if a particular example only reads ranges. This is a sensible precaution when adapting sample code, because later edits may add operations that change workbook contents.

Frequently Asked Questions

What does VBA Intersect return when ranges do not overlap?
It returns Nothing. Store the result in a Range variable and test it with If result Is Nothing Then before trying to use its address, value, or other properties.

Can Application.Intersect check ranges on different worksheets?
No. Its range arguments must be on the same worksheet. Passing ranges from different sheets raises an error; it does not simply return Nothing.

Can Intersect accept more than two ranges?
Yes. It can find the cells common to multiple ranges. A cell must belong to every supplied range to appear in the result, and all of those ranges must be on the same worksheet.

Does Intersect compare the values in cells?
No. It compares the locations covered by the ranges. To find equal values in different cells, use value comparisons or another suitable lookup or search approach.

Why does my Intersect code give an error?
Check that each argument is a valid Range object and that all ranges belong to the same worksheet. Also make sure the result is assigned with Set and checked with Is Nothing before you access it.

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.