Excel VBA Check if a Cell is in a Range

Add to Favorites
Author: | Edits: don

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

ab8ca0d4d1e3775d79e3940a9f28a0cf.jpg

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Logical Operators in Excel VBA Macros
Tutorial: Logical operators in VBA allow you to make decisions when certain conditions are met. They...
Make a UserForm in Excel
Tutorial: Let's create a working UserForm in Excel. This is a step-by-step tutorial that shows you e...
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
Macro: Determine if a cell in Excel contains a formula or function with this UDF (user defined fu...
VBA Comparison Operators
Tutorial: VBA comparison operators are used to compare values in VBA and Macros for Excel. List of V...
Sum Values that Equal 1 of Many Conditions across Multiple Columns in Excel
Tutorial: How to Sum values using an OR condition across multiple columns, including using OR with ...