Intersect Range Name - return range name

[Ziggy12]

How do I find out which of the two ranges the target is in?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim IPJumpRange As Range
Dim OPJumpRange As Range

Set IPJumpRange = Range("B5:B14")
Set OPJumpRange = Range("B19:B28")

If Application.Sum(Range(Target.Offset(0, 1), Target.Offset(0, 14))) = 0 Then
Response = MsgBox("No volume data to drilldown on", vbOKOnly, "Select Drilldown")
Exit Sub
End If

If Intersect(Target, Union(IPJumpRange, OPJumpRange)) Is Nothing Then Exit Sub
SelectedSite = Target.Value

//I need to ID which range the target is in - the IP or OP JumpRange//

UpdatePgeFlds
End Sub

ziggy

 

[Ziggy12]

Re: Intersect Range Name - return range name. Is active cell in range

Add a function
Function InRange(IPJumpRange As Range, OPJumpRange As Range) As Boolean
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(IPJumpRange, OPJumpRange)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function

then a bit of code to call it
If InRange(ActiveCell, Range("B5:B14")) Then JumpTo = "IP"
If InRange(ActiveCell, Range("B19:B28")) Then JumpTo = "OP"

Maybe asking the question prompts a thought a bout the answer.

 

[AlphaFrog]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim IPJumpRange As Range
    Dim OPJumpRange As Range

    Set IPJumpRange = Range("B5:B14")
    Set OPJumpRange = Range("B19:B28")

    If Application.Sum(Range(Target.Offset(0, 1), Target.Offset(0, 14))) = 0 Then
        Response = MsgBox("No volume data to drilldown on", vbOKOnly, "Select Drilldown")
        Exit Sub
    End If

    If Intersect(Target, Union(IPJumpRange, OPJumpRange)) Is Nothing Then Exit Sub
    SelectedSite = Target.Value

    [COLOR="Green"]'//I need to ID which range the target is in - the IP or OP JumpRange//[/COLOR]
[COLOR="Red"]    If Target.Row < 15 Then
        [COLOR="Green"]' Target is in IP[/COLOR]
    Else
       [COLOR="Green"] ' Target is in OP[/COLOR]
    End If[/COLOR]

    UpdatePgeFlds
    
End Sub

 

[Ziggy12]

Much simpler
Ta

ziggy