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

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