Determin in which named range is the activecell

**stojko89** · 01-24-2013, 08:37 AM

Hi,

Is it possible to determin in which named range is the active cell?
I'm tryn to use this somehow:

Dim x As Range, OP1 As Range, OP2 As Range, OP3 As Range, OP4 As Range
Set PO1 = Range("F11:F26")
Set PO2 = Range("F28:F34")
Set PO3 = Range("F36:F50")...

'Find out in which named range the activecell is and then use this

For each x in One Of The Named Ranges
'And so on

Is this somehow possible?

**OllieB** · 01-24-2013, 08:45 AM

Try

If Not Application.Intersect(ActiveCell, PO1) Is Nothing Then
         MsgBox "in PO1"
     ElseIf Not Application.Intersect(ActiveCell, PO2) Is Nothing Then
          MsgBox "in PO2"
     ElseIf Not Application.Intersect(ActiveCell, PO3) Is Nothing Then
          MsgBox "in PO3"
     End If

**OllieB** · 01-24-2013, 08:49 AM

Or alternatively

Dim varRange As Variant
For Each varRange In Array("F11:F26", "F28:F34", "F36:F50")
    If Not Application.Intersect(Range(varRange), ActiveCell) Is Nothing Then
        MsgBox "In range " & varRange
    End If
Next varRange

**stojko89** · 01-24-2013, 09:00 AM

I was so close. But got one little part wrong

But this is how it will do now!

So thanks for the help.

Sub testing()
Dim x As Range, PO1 As Range, PO2 As Range, NR As Range
Set PO1 = Range("F11:F26")
Set PO2 = Range("F28:F40")

If Not Intersect(ActiveCell, PO1) Is Nothing Then
    Set NR = PO1
    MsgBox "in PO1"
ElseIf Not Intersect(ActiveCell, PO2) Is Nothing Then
    Set NR = PO2
    MsgBox "in PO2"
End If
End Sub