Excel VBA Intersect Function: How It Works

VBA & macros · Updated November 21, 2026

In Excel VBA, Application.Intersect returns the cells shared by two or more ranges on the same worksheet. If the ranges do not overlap, it returns Nothing, so assign the result to a Range variable and check it with Is Nothing before using it.

What Intersect Does in VBA

Intersect is a VBA method for finding the overlap between ranges. For example, the intersection of A1:C5 and C3:D6 is C3:C5: those are the cells present in both ranges. This is useful when a macro needs to act only on cells that meet two location-based conditions, such as cells both selected by a user and inside a monitored input area.

The method returns a Range object, not a cell value or a Boolean answer. That means you can inspect its address, loop through its cells, or apply formatting to it. If there is no shared cell, the result is the special object value Nothing. It is not an empty range, and trying to use it as if it were a range can cause a runtime error.

Despite the familiar name, Intersect is not an Excel worksheet formula. You call it from VBA, usually as Application.Intersect(range1, range2). It is particularly common in worksheet event procedures, where it helps determine whether a changed or selected cell falls within a particular range.

Syntax and Requirements

The basic syntax is Application.Intersect(Arg1, Arg2, ...). Each argument must be a Range object, and the ranges must refer to the same worksheet. You can supply two or more range arguments to find cells common to all of them. For instance, intersecting a selected range with a data-entry range and a second restriction range returns only the cells shared by all three.

In VBA, use Set when assigning the result because the method returns an object. Also fully qualify range references with a worksheet. A bare Range("A1:A10") refers to the active sheet, which can change while a macro runs. Qualifying ranges makes the intended worksheet explicit and helps avoid accidental cross-sheet references.

  • Same worksheet: all supplied ranges must be on one sheet. Passing ranges from different worksheets raises an error rather than returning Nothing.
  • Range objects: pass ranges, not text addresses or cell values. Use ws.Range("A1:A10") to create a Range reference.
  • Object check: test for no result with If overlap Is Nothing Then, not by comparing the result with an empty string.

A Basic Working Example

This macro compares two ranges on a worksheet named Sheet1 and reports the shared address. With A1:C5 and C3:D6 as the two ranges, the intersection is C3:C5. If you run macros, save a copy of the workbook first; macro actions cannot be undone with Ctrl+Z, even when a particular example only displays a message.

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

    ' Use ranges on the same worksheet.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set firstRange = ws.Range("A1:C5")
    Set secondRange = ws.Range("C3:D6")

    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

To try a no-overlap case, change the second range to ws.Range("D3:E6"). The message then reports no overlap. The worksheet name must exist in the workbook; otherwise VBA stops when it tries to set ws. The Address(False, False) arguments omit dollar signs, so the result is displayed as C3:C5 rather than $C$3:$C$5.

Using Intersect in a Worksheet Event

A common application is checking whether an edited cell lies inside a watched area. In the following example, paste the procedure into the code module for the worksheet where it should run, not into a standard module. When a user edits any cell in B2:B20, the message identifies the changed cells that overlap that range. If an edit is made elsewhere, the procedure exits without displaying anything.

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

    ' Limit the check to the input cells on this worksheet.
    Set watched = Me.Range("B2:B20")
    Set changedCells = Application.Intersect(Target, watched)

    If changedCells Is Nothing Then Exit Sub

    MsgBox "Changed cells in the watched area: " & _
           changedCells.Address(False, False)
End Sub

Excel passes Target to this event. It can contain more than one cell, for example when a user pastes a block of values, so the intersection can also contain multiple cells. The event example handles that by reporting the whole overlapping address rather than assuming that only one cell changed.

If you adapt an event macro to write to cells, be careful: changing a cell inside a change event can trigger another change event. A common safeguard is to turn Application.EnableEvents off before making the programmatic edit, then turn it back on in a cleanup section that also runs after errors. Do not leave events disabled; otherwise Excel may stop running other worksheet events until they are restored.

Multiple Ranges and Multi-Area Results

You can use Intersect to test several conditions at once. If a range must be inside both a permitted input area and a particular column, pass all three ranges to the method. The returned cells must be common to every argument; this is not the same as finding cells that match any one of the ranges.

Ranges can also consist of multiple areas, such as a selection created with Ctrl-click. The result may likewise contain multiple areas. A range’s Areas collection lets you process those pieces separately when a macro needs to loop over them. For example, a loop over overlap.Areas can apply an action once to each contiguous portion rather than treating the result as one uninterrupted rectangle.

For ordinary cell-by-cell work, a loop over overlap.Cells is also possible. Keep in mind that a large intersection may contain many cells, so a loop that performs slow actions such as repeated screen updates or external lookups can take time. When the goal is a simple formatting change, applying the format to the returned Range directly is often simpler.

Common Errors and How to Avoid Them

  • Ranges from different sheets: Intersect cannot compare ranges on separate worksheets. It raises an error, so check that every range was built from the same worksheet object before calling it.
  • Using a Nothing result: If there is no overlap, overlap.Address is invalid. Always branch on Is Nothing before reading properties or calling methods on the result.
  • Unqualified references: An unqualified Range uses the active sheet. If the active sheet is not the one expected, the macro may inspect the wrong cells or trigger a cross-sheet error. Use a worksheet variable and its Range property.
  • Forgetting Set: Because the result is an object, assign it with Set overlap = Application.Intersect(...). Omitting Set causes an assignment error.
  • Assuming Target is one cell: Pasting into a block makes Target multi-cell. Intersect is useful partly because it handles the whole changed range; avoid code that blindly reads a single cell from it unless that is intentional.

When debugging, inspect the worksheet attached to each range and the addresses of the arguments before the Intersect call. In the Visual Basic Editor, the Immediate window can help examine expressions while code is paused. A cross-sheet error is different from a legitimate non-overlap: the former means the inputs are invalid for the method, while the latter correctly produces Nothing.

Intersect Compared with Other Checks

Use Intersect when you need the actual overlapping Range, not merely a yes-or-no answer. A test such as checking whether one address equals another does not handle partial overlap, larger selections, or multi-area ranges as naturally. Intersect expresses the intended operation directly and gives the macro cells to work with if overlap exists.

For a worksheet formula, use normal formula logic rather than trying to call the VBA method. Excel formulas can test values or compare row and column positions, but there is no worksheet function named Intersect that returns a Range object. Conversely, VBA Intersect does not determine whether cell contents match; it compares the locations represented by the range references.

For large data tasks, consider whether a loop, a table operation, or a formula-based solution fits better. Intersect is a range-location tool, not a replacement for searching values, filtering records, or testing whether two text entries are equal.

Where to Put and Run the Code

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic. If the Developer tab is hidden, it can be enabled through Excel Options. Put a standalone macro such as CheckRangeOverlap in a standard module; put a Worksheet_Change procedure in the code module for the relevant sheet.

On Mac, the Visual Basic Editor shortcut is generally Option+F11; depending on the keyboard and function-key settings, you may need Fn+Option+F11. You can also open the editor from the Developer tab. Excel for the web cannot create or run VBA macros, so open the workbook in the desktop application to use this code.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code. Follow your organization’s macro-security rules and run only code you trust. The examples here access ranges and display messages; any added code that changes data should be reviewed carefully and tested on a copy.

Frequently Asked Questions

Does Excel have an Intersect worksheet function?
No. Application.Intersect is a VBA method that returns a Range object. It cannot be entered as a normal cell formula.

What does Intersect return when ranges do not overlap?
It returns Nothing. Check the object with If result Is Nothing Then before trying to use its address, cells, or formatting.

Can Intersect work with ranges on different worksheets?
No. The range arguments must be on the same worksheet. If they refer to different sheets, VBA raises an error rather than returning an empty result.

Can Intersect handle a multi-cell selection?
Yes. It returns the portion of the selection shared with the other range, which may contain one cell, a rectangular block, or multiple areas. Do not assume the event’s Target is always a single cell.

Why does my Intersect code say object variable not set?
A likely cause is that the ranges do not overlap and the result is Nothing, but the code then tries to use it. Add an Is Nothing check. Also verify that the range variables were assigned with Set and refer to the intended worksheet.

Related

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.