VBA test if cell is in a range

From the Help:

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

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 IfEnd Sub

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 FunctionSub 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 IfEnd Sub