I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Hi Robert,

If "IMP_" is always going to be at the left of the string and "_ID" at
the right then you could do away with the Find methos altogether and
just use the Left() and Right() functions. If not then perhaps you can
use the Mid() function.

For the second condition you could use a nested If statement or simply
use an AND statement in the first If statement.

Let me know if you can't work it out.

NickH


wrote:
I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Thanks Tom, I Have got that working. Im using Activesheet because
there are many sheets from on template that have the same names and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet? or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire active sheet
instead of your named ranges. You will get "false positives" on many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet because
there are many sheets from on template that have the same names and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

I didn't get all the changes incorporated that I had intended:

Sub aBC()
Dim zNmRng As Name
Dim zNmRngA As Range
Dim sNmRngB As Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
Set zNmRngB = zNmRng.RefersToRange
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
After:=zNmRngB(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not zNmRngA Is Nothing Then

'Conditional formating code goes here

End If
End If
Next

End Sub

The above worked fine for me.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet? or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire active
sheet
instead of your named ranges. You will get "false positives" on many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet because
there are many sheets from on template that have the same names and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for the
ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating
part
working but Im having trouble iterating through the names for the
text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is
true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part,
the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Hi Tom,

Much better. But you still need to set "zNmRngB" to Nothing before your
error trapping to avoid unwanted false positives...

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

I didn't get all the changes incorporated that I had intended:

Sub aBC()
Dim zNmRng As Name
Dim zNmRngA As Range
Dim sNmRngB As Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
Set zNmRngB = zNmRng.RefersToRange
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
After:=zNmRngB(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not zNmRngA Is Nothing Then

'Conditional formating code goes here

End If
End If
Next

End Sub

The above worked fine for me.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet? or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire active
sheet
instead of your named ranges. You will get "false positives" on many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet because
there are many sheets from on template that have the same names and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for the
ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating
part
working but Im having trouble iterating through the names for the
text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is
true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part,
the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Nick, I ended up working the Toms sugestion. However, I would like to
see you would use the Left() Right() and Mid functions in this case, if
you have time... I will certainly want to learn to use them.

Thanks
Robert



NickH wrote:
Hi Robert,

If "IMP_" is always going to be at the left of the string and "_ID" at
the right then you could do away with the Find methos altogether and
just use the Left() and Right() functions. If not then perhaps you can
use the Mid() function.

For the second condition you could use a nested If statement or simply
use an AND statement in the first If statement.

Let me know if you can't work it out.

NickH


wrote:
I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Luc, I only want to look at local names, in this case. Here is my code
as is, working or so it seems :)

Dim zNm As Name
Dim zNmRng As Range
Dim zNmRngA As Range
Dim zNmRngB As Range

For Each zNm In ActiveSheet.Names ' Iterate through each
element.
On Error Resume Next
Set zNmRngB = zNm.RefersToRange
'zNmRngBAdd = zNmRngB.Address
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not zNmRngA Is Nothing Then

'Conditional formating code

Range(zNmRngA).Select
Selection.FormatConditions.Delete
'LCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" &
Selection.End(xlDown).Offset(12, 0).Address
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With

Selection.FormatConditions(1).Interior.ColorIndex = 22

'UCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" &
Selection.End(xlDown).Offset(11, 0).Address

Selection.FormatConditions(2).Interior.ColorIndex = 19

'end Conditional formating code

End If
End If
Next


PapaDos wrote:
It may actually look in LESS names than you intended.
"Sheet.names" only returns the "local" names, like "Sheet2!my_range" and not
the names define at the workbook level like "my_range".
To define those names in Excel's user interface you have to enter the
sheetname at the begining of the NAME...

By the way, Tom's code has a few flaws... worst than mine ! ;-]
If you are basing your code on his, post your code, if you want help in
correcting its behaviour...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet? or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire active sheet
instead of your named ranges. You will get "false positives" on many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet because
there are many sheets from on template that have the same names and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

It doesn't happen often,

So making a generalized statement condemning RefersToRange without
qualification seems a disservice to the OP. It is certainly a useful
property when referring to names in other workbooks and other times as well.

--
Regards,
Tom Ogilvy




"PapaDos" wrote in message
...
It doesn't happen often, I think it is related to the 255 characters limit
of
the "RefersTo" property. In some situations, the refersToRange property
can
even generate an error, even if the named range is working properly...

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

Then that would be a bug that I have never run into and I have never seen
mentioned anywhere. It always works for me and it the purpose of the
property.

--
regards,
Tom Ogilvy


"PapaDos" wrote:

One more thing to consider, it happens that
activesheet.names(X).referstorange and range("X") are different sizes
ranges.
The valid one should be range("X").

So, you should set "zNmRngB" to Range(zNmRng.name) before calling the
Find()
method on it...
--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

I didn't get all the changes incorporated that I had intended:

Sub aBC()
Dim zNmRng As Name
Dim zNmRngA As Range
Dim sNmRngB As Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
Set zNmRngB = zNmRng.RefersToRange
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
After:=zNmRngB(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not zNmRngA Is Nothing Then

'Conditional formating code goes here

End If
End If
Next

End Sub

The above worked fine for me.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet?
or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire
active
sheet
instead of your named ranges. You will get "false positives" on
many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet
because
there are many sheets from on template that have the same names
and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for
the
ones
that contain certain text, in this case "IMP" then I need to
set a
conditonal format to the range. I have the conditonal
formating
part
working but Im having trouble iterating through the names
for the
text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first
conditon is
true
then I need to see if the name has "ID" in the name and if
so skip
the code. Im sure that once I figure out how to do the
first part,
the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Useful but flawed.
And in the present situation, it may cause valid named ranges to be skipped.
That's a disservice to the OP for you ?
LOL

Try this little experiment:
- Name a blank worksheet "ThisIsARatherLongNameForASheet"
- Select the range $A$1,$B$2,$C$3,$D$4,$E$5,$F$6,$G$7 and give it the a name
using the "Name box" of the formula bar.

Now in VBA use the range() property and the Names().referstoRange property
to Select that new named range, see what happens...

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

It doesn't happen often,

So making a generalized statement condemning RefersToRange without
qualification seems a disservice to the OP. It is certainly a useful
property when referring to names in other workbooks and other times as well.

--
Regards,
Tom Ogilvy




"PapaDos" wrote in message
...
It doesn't happen often, I think it is related to the 255 characters limit
of
the "RefersTo" property. In some situations, the refersToRange property
can
even generate an error, even if the named range is working properly...

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

Then that would be a bug that I have never run into and I have never seen
mentioned anywhere. It always works for me and it the purpose of the
property.

--
regards,
Tom Ogilvy


"PapaDos" wrote:

One more thing to consider, it happens that
activesheet.names(X).referstorange and range("X") are different sizes
ranges.
The valid one should be range("X").

So, you should set "zNmRngB" to Range(zNmRng.name) before calling the
Find()
method on it...
--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

I didn't get all the changes incorporated that I had intended:

Sub aBC()
Dim zNmRng As Name
Dim zNmRngA As Range
Dim sNmRngB As Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
Set zNmRngB = zNmRng.RefersToRange
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
After:=zNmRngB(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not zNmRngA Is Nothing Then

'Conditional formating code goes here

End If
End If
Next

End Sub

The above worked fine for me.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet?
or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire
active
sheet
instead of your named ranges. You will get "false positives" on
many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet
because
there are many sheets from on template that have the same names
and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for
the
ones
that contain certain text, in this case "IMP" then I need to
set a
conditonal format to the range. I have the conditonal
formating
part
working but Im having trouble iterating through the names
for the
text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first
conditon is
true
then I need to see if the name has "ID" in the name and if
so skip
the code. Im sure that once I figure out how to do the
first part,
the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

I would replace a few lines to correct possible flaws:

For Each zNm In ActiveSheet.Names ' Iterate through each element.
Set zNmRngB = Nothing ' ***ADDED***
On Error Resume Next
Set zNmRngB = range(zNm.name) ' ***MODIFIED***
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False) ' ***MODIFIED***

.......... rest of your code is unchanged, I didn't look at your "formating
code" .....

--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, I only want to look at local names, in this case. Here is my code
as is, working or so it seems :)

Dim zNm As Name
Dim zNmRng As Range
Dim zNmRngA As Range
Dim zNmRngB As Range

For Each zNm In ActiveSheet.Names ' Iterate through each
element.
On Error Resume Next
Set zNmRngB = zNm.RefersToRange
'zNmRngBAdd = zNmRngB.Address
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not zNmRngA Is Nothing Then

'Conditional formating code

Range(zNmRngA).Select
Selection.FormatConditions.Delete
'LCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" &
Selection.End(xlDown).Offset(12, 0).Address
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With

Selection.FormatConditions(1).Interior.ColorIndex = 22

'UCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" &
Selection.End(xlDown).Offset(11, 0).Address

Selection.FormatConditions(2).Interior.ColorIndex = 19

'end Conditional formating code

End If
End If
Next


PapaDos wrote:
It may actually look in LESS names than you intended.
"Sheet.names" only returns the "local" names, like "Sheet2!my_range" and not
the names define at the workbook level like "my_range".
To define those names in Excel's user interface you have to enter the
sheetname at the begining of the NAME...

By the way, Tom's code has a few flaws... worst than mine ! ;-]
If you are basing your code on his, post your code, if you want help in
correcting its behaviour...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet? or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire active sheet
instead of your named ranges. You will get "false positives" on many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet because
there are many sheets from on template that have the same names and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

I don't need the contrived experiment. I admitted that that is a
limitation. Nonetheless, it is a contrived experiment and not one
frequently encountered. Well worth knowing about, but certainly not a
generalized truth and highly unlikely in this situation as demonstrated by
the OP. Lay the facts on the table and let the OP make an informed
decision. Fortunately, this discussion has done that.

--
Regards,
Tom Ogilvy



"PapaDos" wrote in message
...
Useful but flawed.
And in the present situation, it may cause valid named ranges to be
skipped.
That's a disservice to the OP for you ?
LOL

Try this little experiment:
- Name a blank worksheet "ThisIsARatherLongNameForASheet"
- Select the range $A$1,$B$2,$C$3,$D$4,$E$5,$F$6,$G$7 and give it the a
name
using the "Name box" of the formula bar.

Now in VBA use the range() property and the Names().referstoRange property
to Select that new named range, see what happens...

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

It doesn't happen often,

So making a generalized statement condemning RefersToRange without
qualification seems a disservice to the OP. It is certainly a useful
property when referring to names in other workbooks and other times as
well.

--
Regards,
Tom Ogilvy




"PapaDos" wrote in message
...
It doesn't happen often, I think it is related to the 255 characters
limit
of
the "RefersTo" property. In some situations, the refersToRange property
can
even generate an error, even if the named range is working properly...

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

Then that would be a bug that I have never run into and I have never
seen
mentioned anywhere. It always works for me and it the purpose of
the
property.

--
regards,
Tom Ogilvy


"PapaDos" wrote:

One more thing to consider, it happens that
activesheet.names(X).referstorange and range("X") are different
sizes
ranges.
The valid one should be range("X").

So, you should set "zNmRngB" to Range(zNmRng.name) before calling
the
Find()
method on it...
--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

I didn't get all the changes incorporated that I had intended:

Sub aBC()
Dim zNmRng As Name
Dim zNmRngA As Range
Dim sNmRngB As Range

For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
Set zNmRngB = zNmRng.RefersToRange
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
After:=zNmRngB(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not zNmRngA Is Nothing Then

'Conditional formating code goes here

End If
End If
Next

End Sub

The above worked fine for me.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active
sheet?
or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire
active
sheet
instead of your named ranges. You will get "false positives" on
many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet
because
there are many sheets from on template that have the same
names
and I
may need to run diffent pass/fail criteria on differnt
sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and
for
the
ones
that contain certain text, in this case "IMP" then I need
to
set a
conditonal format to the range. I have the conditonal
formating
part
working but Im having trouble iterating through the names
for the
text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
If zNmRng = Cells.Find(what:="IMP",
After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first
conditon is
true
then I need to see if the name has "ID" in the name and
if
so skip
the code. Im sure that once I figure out how to do the
first part,
the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Set zNmRngB = range(zNm.name)

should be
Set zNmRngB = ActiveSheet.range(zNm.name)

using Range(zNm.name) in a sheet module implies

me.Range(zNm.name)

If the activesheet isn't the sheet containing the code, then this would
raise an error.

This raises and error:

Private Sub CommandButton1_Click()
Worksheets("Movies").Activate
For Each nm In ActiveSheet.Names
Set rng = Range(nm.Name)
Next
End Sub

This doesn't:

Private Sub CommandButton1_Click()
Worksheets("Movies").Activate
For Each nm In ActiveSheet.Names
Set rng = Activesheet.Range(nm.Name)
Next
End Sub

--
Regards,
Tom Ogilvy



"PapaDos" wrote in message
...
I would replace a few lines to correct possible flaws:

For Each zNm In ActiveSheet.Names ' Iterate through each element.
Set zNmRngB = Nothing ' ***ADDED***
On Error Resume Next
Set zNmRngB = range(zNm.name) ' ***MODIFIED***
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False) ' ***MODIFIED***

......... rest of your code is unchanged, I didn't look at your "formating
code" .....

--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, I only want to look at local names, in this case. Here is my code
as is, working or so it seems :)

Dim zNm As Name
Dim zNmRng As Range
Dim zNmRngA As Range
Dim zNmRngB As Range

For Each zNm In ActiveSheet.Names ' Iterate through each
element.
On Error Resume Next
Set zNmRngB = zNm.RefersToRange
'zNmRngBAdd = zNmRngB.Address
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not zNmRngA Is Nothing Then

'Conditional formating code

Range(zNmRngA).Select
Selection.FormatConditions.Delete
'LCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" &
Selection.End(xlDown).Offset(12, 0).Address
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With

Selection.FormatConditions(1).Interior.ColorIndex = 22

'UCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" &
Selection.End(xlDown).Offset(11, 0).Address

Selection.FormatConditions(2).Interior.ColorIndex = 19

'end Conditional formating code

End If
End If
Next


PapaDos wrote:
It may actually look in LESS names than you intended.
"Sheet.names" only returns the "local" names, like "Sheet2!my_range"
and not
the names define at the workbook level like "my_range".
To define those names in Excel's user interface you have to enter the
sheetname at the begining of the NAME...

By the way, Tom's code has a few flaws... worst than mine ! ;-]
If you are basing your code on his, post your code, if you want help in
correcting its behaviour...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet?
or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire
active sheet
instead of your named ranges. You will get "false positives" on
many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet
because
there are many sheets from on template that have the same names
and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for
the ones
that contain certain text, in this case "IMP" then I need to
set a
conditonal format to the range. I have the conditonal
formating part
working but Im having trouble iterating through the names for
the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon
is true
then I need to see if the name has "ID" in the name and if so
skip
the code. Im sure that once I figure out how to do the first
part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Thanks Tom for making me "rethink" about the "context".

We should actually use

Set zNmRngB = application.range(zNm.name)

in case there is a name like "Sheet1!my_range" that refers to
"Sheet2!A1:A3", for example. In that context, the activesheet.range(zNm)
would fail...

I hope we got it right in all contexts this time !

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

Set zNmRngB = range(zNm.name)

should be
Set zNmRngB = ActiveSheet.range(zNm.name)

using Range(zNm.name) in a sheet module implies

me.Range(zNm.name)

If the activesheet isn't the sheet containing the code, then this would
raise an error.

This raises and error:

Private Sub CommandButton1_Click()
Worksheets("Movies").Activate
For Each nm In ActiveSheet.Names
Set rng = Range(nm.Name)
Next
End Sub

This doesn't:

Private Sub CommandButton1_Click()
Worksheets("Movies").Activate
For Each nm In ActiveSheet.Names
Set rng = Activesheet.Range(nm.Name)
Next
End Sub

--
Regards,
Tom Ogilvy



"PapaDos" wrote in message
...
I would replace a few lines to correct possible flaws:

For Each zNm In ActiveSheet.Names ' Iterate through each element.
Set zNmRngB = Nothing ' ***ADDED***
On Error Resume Next
Set zNmRngB = range(zNm.name) ' ***MODIFIED***
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False) ' ***MODIFIED***

......... rest of your code is unchanged, I didn't look at your "formating
code" .....

--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, I only want to look at local names, in this case. Here is my code
as is, working or so it seems :)

Dim zNm As Name
Dim zNmRng As Range
Dim zNmRngA As Range
Dim zNmRngB As Range

For Each zNm In ActiveSheet.Names ' Iterate through each
element.
On Error Resume Next
Set zNmRngB = zNm.RefersToRange
'zNmRngBAdd = zNmRngB.Address
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not zNmRngA Is Nothing Then

'Conditional formating code

Range(zNmRngA).Select
Selection.FormatConditions.Delete
'LCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" &
Selection.End(xlDown).Offset(12, 0).Address
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With

Selection.FormatConditions(1).Interior.ColorIndex = 22

'UCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" &
Selection.End(xlDown).Offset(11, 0).Address

Selection.FormatConditions(2).Interior.ColorIndex = 19

'end Conditional formating code

End If
End If
Next


PapaDos wrote:
It may actually look in LESS names than you intended.
"Sheet.names" only returns the "local" names, like "Sheet2!my_range"
and not
the names define at the workbook level like "my_range".
To define those names in Excel's user interface you have to enter the
sheetname at the begining of the NAME...

By the way, Tom's code has a few flaws... worst than mine ! ;-]
If you are basing your code on his, post your code, if you want help in
correcting its behaviour...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet?
or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire
active sheet
instead of your named ranges. You will get "false positives" on
many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet
because
there are many sheets from on template that have the same names
and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and for
the ones
that contain certain text, in this case "IMP" then I need to
set a
conditonal format to the range. I have the conditonal
formating part
working but Im having trouble iterating through the names for
the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first conditon
is true
then I need to see if the name has "ID" in the name and if so
skip
the code. Im sure that once I figure out how to do the first
part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert

Luc, Thanks for that.

That is certainly a more generalized solution.

--
Regards,
Tom Oglvy

"PapaDos" wrote in message
...
Thanks Tom for making me "rethink" about the "context".

We should actually use

Set zNmRngB = application.range(zNm.name)

in case there is a name like "Sheet1!my_range" that refers to
"Sheet2!A1:A3", for example. In that context, the activesheet.range(zNm)
would fail...

I hope we got it right in all contexts this time !

--
Regards,
Luc.

"Festina Lente"


"Tom Ogilvy" wrote:

Set zNmRngB = range(zNm.name)

should be
Set zNmRngB = ActiveSheet.range(zNm.name)

using Range(zNm.name) in a sheet module implies

me.Range(zNm.name)

If the activesheet isn't the sheet containing the code, then this would
raise an error.

This raises and error:

Private Sub CommandButton1_Click()
Worksheets("Movies").Activate
For Each nm In ActiveSheet.Names
Set rng = Range(nm.Name)
Next
End Sub

This doesn't:

Private Sub CommandButton1_Click()
Worksheets("Movies").Activate
For Each nm In ActiveSheet.Names
Set rng = Activesheet.Range(nm.Name)
Next
End Sub

--
Regards,
Tom Ogilvy



"PapaDos" wrote in message
...
I would replace a few lines to correct possible flaws:

For Each zNm In ActiveSheet.Names ' Iterate through each element.
Set zNmRngB = Nothing ' ***ADDED***
On Error Resume Next
Set zNmRngB = range(zNm.name) ' ***MODIFIED***
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False) ' ***MODIFIED***

......... rest of your code is unchanged, I didn't look at your
"formating
code" .....

--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, I only want to look at local names, in this case. Here is my
code
as is, working or so it seems :)

Dim zNm As Name
Dim zNmRng As Range
Dim zNmRngA As Range
Dim zNmRngB As Range

For Each zNm In ActiveSheet.Names ' Iterate through each
element.
On Error Resume Next
Set zNmRngB = zNm.RefersToRange
'zNmRngBAdd = zNmRngB.Address
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not zNmRngA Is Nothing Then

'Conditional formating code

Range(zNmRngA).Select
Selection.FormatConditions.Delete
'LCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" &
Selection.End(xlDown).Offset(12, 0).Address
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With

Selection.FormatConditions(1).Interior.ColorIndex = 22

'UCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" &
Selection.End(xlDown).Offset(11, 0).Address

Selection.FormatConditions(2).Interior.ColorIndex = 19

'end Conditional formating code

End If
End If
Next


PapaDos wrote:
It may actually look in LESS names than you intended.
"Sheet.names" only returns the "local" names, like "Sheet2!my_range"
and not
the names define at the workbook level like "my_range".
To define those names in Excel's user interface you have to enter
the
sheetname at the begining of the NAME...

By the way, Tom's code has a few flaws... worst than mine ! ;-]
If you are basing your code on his, post your code, if you want help
in
correcting its behaviour...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active
sheet?
or
does it still look at a lot of unecessary space?

Thanks for bringing this up
Robert


PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire
active sheet
instead of your named ranges. You will get "false positives" on
many
occasions...
--
Regards,
Luc.

"Festina Lente"


" wrote:

Thanks Tom, I Have got that working. Im using Activesheet
because
there are many sheets from on template that have the same
names
and I
may need to run diffent pass/fail criteria on differnt sheets.

Thanks Again

Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then

'Conditional formating code goes here

End If
end if
Next


You also might Try

ThisWorkbook.Names instead of Activesheet.Names

It depends on which names you want to look at.
--
Regards,
Tom Ogilvy


" wrote:

I need to search through the Named Ranges in a sheet and
for
the ones
that contain certain text, in this case "IMP" then I need
to
set a
conditonal format to the range. I have the conditonal
formating part
working but Im having trouble iterating through the names
for
the text

My code is

Dim zNmRng

For Each zNmRng In ActiveSheet.Names ' Iterate through
names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then

'Conditional formating code goes here

End If
Next

I am obviously using the find method incorrectly


I also want to have a second condition if, the first
conditon
is true
then I need to see if the name has "ID" in the name and if
so
skip
the code. Im sure that once I figure out how to do the
first
part, the
second is just a mater of nesting.

Here are some examples of the named ranges:

IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do

Any help will be appreciated
Robert