[excel] VBA test if cell is in a range

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

This question is related to excel vba

The answer is


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

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

Here is another option to see if a cell exists inside a range. In case you have issues with the Intersect solution as I did.

If InStr(range("NamedRange").Address, range("IndividualCell").Address) > 0 Then
    'The individual cell exists in the named range
Else
    'The individual cell does not exist in the named range
End If

InStr is a VBA function that checks if a string exists within another string.

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/instr-function


@mywolfe02 gives a static range code so his inRange works fine but if you want to add dynamic range then use this one with inRange function of him.this works better with when you want to populate data to fix starting cell and last column is also fixed.

Sub DynamicRange()

Dim sht As Worksheet
Dim LastRow As Long
Dim StartCell As Range
Dim rng As Range

Set sht = Worksheets("xyz")
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Set rng = Workbooks("Record.xlsm").Worksheets("xyz").Range(Cells(12, 2), Cells(LastRow, 12))

Debug.Print LastRow

If InRange(ActiveCell, rng) Then
'        MsgBox "Active Cell In Range!"
  Else
      MsgBox "Please select the cell within the range!"
  End If

End Sub 

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

I don't work with contiguous ranges all the time. My solution for non-contiguous ranges is as follows (includes some code from other answers here):

Sub test_inters()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim inters As Range

    Set rng2 = Worksheets("Gen2").Range("K7")
    Set rng1 = ExcludeCell(Worksheets("Gen2").Range("K6:K8"), rng2)

    If (rng2.Parent.name = rng1.Parent.name) Then
        Dim ints As Range
        MsgBox rng1.Address & vbCrLf _
        & rng2.Address & vbCrLf _

        For Each cell In rng1
            MsgBox cell.Address
            Set ints = Application.Intersect(cell, rng2)
            If (Not (ints Is Nothing)) Then
                MsgBox "Yes intersection"
            Else
                MsgBox "No intersection"
            End If
        Next cell
    End If
End Sub