Excel VBA Check if a Cell is in a Range

VBA that checks if a cell is in a range, named range, or any kind of range, in Excel.

Sections:

Check if Cell is in a Range Macro

Check if Cell is in a Named Range

Notes

Check if Cell is in a Range Macro

Sub CellinRange()

'######################################'
'#########   TeachExcel.com   #########'
'######################################'

'Tests if a Cell is within a specific range.

Dim testRange As Range
Dim myRange As Range

'Set the range
Set testRange = Range("B3:D6")

'Get the cell or range that the user selected
Set myRange = Selection

'Check if the selection is inside the range.
If Intersect(testRange, myRange) Is Nothing Then
    'Selection is NOT inside the range.

    MsgBox "Selection is outside the test range."

Else
    'Selection IS inside the range.

    MsgBox "Selection is inside the test range."

End If

End Sub

How to Use the Macro

B3:D6 change this to the range that you want to check to see if the cell is within it.

Selection keep this the same to use the currently selected cell or range from the user or change it to a regular hard-coded range reference like Range("A1") or whatever range you want.

'Selection is NOT inside the range. under this line, put the code that you want to run when the cell is not within the range.

'Selection IS inside the range. under this line, put the code that you want to run when the cell is within the range.

How Does the Macro Know if the Cell is in the Range?

Intersect(testRange, myRange) this checks if the range within the myRange variable overlaps or intersects with the range in the testRange variable. This function, the Intersect function, will return a range object where the two ranges overlap, or, if they don't overlap, it will return nothing.

As such, we can use the IF statement to check if nothing was returned or not and we do that using Is Nothing after the Intersect function. The Is Nothing is what checks if the Intersect function returned nothing or not, which evaluates to TRUE or FALSE, which is then used by the IF statement to complete its logic and run.

This is how we get the full first line of the IF statement, and this is where all the magic happens:

If Intersect(testRange, myRange) Is Nothing Then

The rest of the IF statement is just a regular IF statement in VBA, with a section of code that runs if the cell is within the range and one that runs when the cell is outside of the range.

Check if Cell is in a Named Range

This is almost exactly the same as the previous macro except that we need to change the range reference from B3:D6 to the name of the named range; that's it.

It could look like this:

Sub CellinRange()

'######################################'
'#########   TeachExcel.com   #########'
'######################################'

'Tests if a Cell is within a specific range.

Dim testRange As Range
Dim myRange As Range

'Set the range
Set testRange = Range("MyNamedRange")

'Get the cell or range that the user selected
Set myRange = Selection

'Check if the selection is inside the range.
If Intersect(testRange, myRange) Is Nothing Then
    'Selection is NOT inside the range.

    MsgBox "Selection is outside the test range."

Else
    'Selection IS inside the range.

    MsgBox "Selection is inside the test range."

End If

End Sub

For a more detailed expanation of how this macro works, look to the section above.

Notes

If the user makes a selection of multiple cells or a range, and any of those cells are within the test range, this macro will run the code for when the selection is inside the range.

Testing if a range is within a range simply uses the Intersect function in Excel VBA; however, due to how it works, it can seem confusing since we have to check if it Is Nothing or not. But, once you get used to writing this and seeing this in IF statements, it's really not that difficult, just remember that, in this instance, you will want to pair the use of Intersect with Is Nothing and everything else should follow quite easily.

Download the sample file to get the macro in Excel.