VBA test if cell is in a range

Question

I want to test if a given cell is within a given range in Excel VBA. What is the best way to do this?

Answer 1

From the Help:

Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
    MsgBox "Ranges do not intersect"
Else
    isect.Select
End If

Answer 2

If the two ranges to be tested (your given cell and your given range) are not in the same Worksheet, then Application.Intersect throws an error. Thus, a way to avoid it is with something like

Sub test_inters(rng1 As Range, rng2 As Range)
    If (rng1.Parent.Name = rng2.Parent.Name) Then
        Dim ints As Range
        Set ints = Application.Intersect(rng1, rng2)
        If (Not (ints Is Nothing)) Then
            ' Do your job
        End If
    End If
End Sub

Answer 3

Determine if a cell is within a range using VBA in Microsoft Excel:

From the linked site (maintaining credit to original submitter):

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions

Function InRange(Range1 As Range, Range2 As Range) As Boolean
    ' returns True if Range1 is within Range2
    InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
End Function


Sub TestInRange()
    If InRange(ActiveCell, Range("A1:D100")) Then
        ' code to handle that the active cell is within the right range
        MsgBox "Active Cell In Range!"
    Else
        ' code to handle that the active cell is not within the right range
        MsgBox "Active Cell NOT In Range!"
    End If
End Sub