Is ActiveCell within Named Range?

[MatthewBell]

Hi there

An easy one hopefully

Is there a simple command to check if a given cell is within a named range?

I want to write event handler procedures that will only be executed when the Selection_Change is to a cell that is on a row which intersects with a range. The range in question covers a number of cells within a single column.

I'm fine locating the intersection of the row of the selected cell and the column of the named range, I just need an efficient way of checking if that intersection is within the named range.

Can anyone help?

Many thanks

Matt :-/

 

[SkipVought]

Hi,

Use Intersection...

    Dim rng As Range
    Set rng = Intersect(Range1, Range2)
    If rng Is Nothing Then
        'no intersection
    Else
        'we have an intersection
    End If

hope this helps Skip,
Skip@theofficeexperts.com

 

[xlbo]

Directly from VBA help:

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

sooooo......
Set isect = Application.Intersect(Range("namedrange&quot, Range("NamedRangeColumn" & target.row))
If isect Is Nothing Then
exit sub
Else
'run code
End If Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bryanbayfield]

Could you not be a little more cute and write:

If Application.Intersect(Range("namedrange"), Range("NamedRangeColumn" & target.row)) Is Nothing Then ...

???

 

[bryanbayfield]

In fact you could be even cuter and drop the "Application" since Intersect is a global method.

 

[MatthewBell]

Okay. Thanks everyone

Your enthusiasm is overwhelming but i'm not quite getting why it needs this bit for Range2. If i put in:

Range(("RangeName&quot.Column & Target.row) it doesn't work. Am I missing the point of the NamedRangeColumn bit.

I was expecting range2 to just be Target.row but this doesn't work either.

 

[SkipVought]

A range can be "A1:C5", or "C:C" or a NAME that has been assigned to a range.

So, if you have NAMED a range (lets say "MyRange&quot that you want to detect a Selection in then...

set rng = Intersect(Range("MyRange"), Target)

along with all the above... Skip,
Skip@theofficeexperts.com

 

[xlbo]

I suggested:
Set isect = Application.Intersect(Range("namedrange&quot, Range("NamedRangeColumn" & target.row))
as you said you wanted to check a selected cell against whether the row that the cell is on, intersects with a named range (vertical). Lets say the named range covers K100:K200
Would I be right in assuming that you want this code to run if B102 is selected but not if B99 is selected ??
This being the case, you could write:
Set isect = Intersect(Range("K100:K200&quot, Range("K" & target.row))

you need to hardcode the Target COLUMN as otherwise therewill be no intersect 'cos you are using a 1 column named range

So, if your named range is called myChkRng
then
Set isect = Intersect(Range("myChkRng&quot, Range("K" & target.row))
should work

Hope this clarifies Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[MatthewBell]

Cheers guys.
Sorry for the delayed reply but I finished work and have only just started again.

Between the selection of answers I patched together an understanding and ended up with.

If Intersect(Range("RangeName&quot, Cells(Target.Row, Range("RangeName&quot.Column)) Is Nothing Then Exit Sub

I'm hoping this gives the robustness I require. I didn't want to hardcode the column as you suggested xlbo as the range may move when other columns are inserted etc.

If you can see any faults with this I'd be only too pleased to receive any further improvements. Otherwise thanks for the help y'all.

Matt