A Name has a property called RefersToRange which is a reference to the cells
defined in the Name. When you loop through the names, you need to use the
Intersect method to see if some cell, such as the ActiveCell, is within the
Name's RefersToRange. Intersect takes two (or more) ranges and returns as it
result those cells that are in both (all) input ranges. For example,

Application.Intersect(Range("A1:D4"), Range("C3:F8"))

returns a reference to the range C3:D4 since those cells a common to both
A1:D4 and C3:F8. If there are no cells in common, Intersect returns a
special value called Nothing.

Therefore, you can use code like the following.


Sub AAA()
Dim NN As Name
Dim R As Range
For Each NN In ThisWorkbook.Names
Set R = Application.Intersect(NN.RefersToRange, ActiveCell)
If R Is Nothing Then
' no common cells in NN and ActiveCell
Debug.Print "ActiveCell is NOT within Name: " & _
NN.Name & "(" & NN.RefersToRange.Address & ")"
Else
' ActiveCell within NN.RefersToRange
Debug.Print "ActiveCell is within Name: " & _
NN.Name & "(" & NN.RefersToRange.Address & ")"
End If
Next NN
End Sub


This loops through all the Names in the workbook, assigning the Name to the
variable NN, and then uses Intersect between NN.RefersToRange and ActiveCell
to determine whether ActiveCell is within the range defined by NN. In the
code I wrote in my previous reply, I handled the case in which a cell may be
within two named ranges. That's why the code used an array of Name objects.
(E.g., if NameOne is A1:D4 and NameTwo is B2:F4, cell C3 is within both
NameOne and NameTwo.)

The code above examines ALL named ranges in the workbook. If you want only
to examine a few Names, store the names of the Names in an array and loop
through that. E.g.,


Sub BBB()
Dim Arr As Variant
Dim N As Long
Dim R As Range
Arr = Array("NameOne", "NameTwo", "NameThree")
For N = LBound(Arr) To UBound(Arr)
Set R = Application.Intersect(Range(Arr(N)), ActiveCell)
If R Is Nothing Then
Debug.Print "ActiveCell not within range: " & Arr(N)
Else
Debug.Print "ActiveCell is within range: " & Arr(N)
End If
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






"MJKelly" wrote in message
...
Thanks Guys,
I am still quite confused. I am looping through set named ranges (a
cell only appears in one named range). I am not selecting any cells.
So

For each C in ranges (named ranges)
do stuff

How can I determin if C is in named range1 or named range2?

if in named range 1 then put values in named range 3
if in named range 2 then put values in named range 4

Matt