What range is a cell in?

P

ppsza

Concerning Excel VBA: Does anyone know of a way that I can find ou
what range a particular cell (ActiveCell) is in? Here's th
situation: I have several named ranges in a worksheet. When a cel
changes, if it is in a particular range, then some code needs to b
executed, and, if it's in another range, other code needs to b
executed

Thanks
 
D

Dennis

I looked at the link:

If Intersect(Activecell,Range("Name1") Then
'do something
ElseIf Intersect(Activecell,Range("Name2") Then
'do something else
etc.

Is it possible to replace the 'Range("Name1")' Range
("a1:10")' or suchlike?
 
B

Bob Phillips

Dennis,

Indeed it is. The previous poster ws using named ranges, that is why we used
that syntax. The other post is not absolutely correct, this is (I hope)

If Not Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
'do something
ElseIf Not Intersect(ActiveCell, Range("B1:B10")) Is Nothing Then
'do something else
etc.

It works in the same way

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

It actually has an error, it should be

If Not Intersect(ActiveCell, Range("Name1")) Is Nothing Then
'do something
ElseIf Not Intersect(ActiveCell, Range("Name2")) Is Nothing Then
'do something else
etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top