Finding all Similarly Named Ranges?

[Grant Reid]

Hi

I have a workbook in which I have defined similar named ranges in may named
worksheets, they all start with "H1_Server". What I am trying to do is code
a procedure that will go through all the named worksheets in the workbook,
selecting the named range if found, and then applying arbitary formatting
(lets just say change the background color to green).

Can anyone out there help?

Many Thanks - Grant

 

[Grant Reid]

Hi

In my workbook I have two named worksheets, "Extract" and "Info".

On the worksheet "Extract" I have three named Ranges, "H1_Server_1",
"H1_Server_2", "H1_Server_3"

On the worksheet "Extract" I have three named Ranges, "H1_Server_4",
"H1_Server_5", "H1_Server_6"

I need to fashion a script that will locate any named range starting with
"H1_Server_" on any worksheet and then change the background color of the
named ranges.

This is the code that I have at the moment but it only seems to work on the
worksheet "Extract".

Sub FormatH1()
Dim nm As String
Dim x As Integer
Dim strH1 As String

strH1 = "H1_Server_"

For x = 1 To ActiveWorkbook.Names.Count
On Error GoTo lineend:
nm = ActiveWorkbook.Names(x).Name
If InStr(nm, strH1) Then
Range(nm).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next
lineend:
End Sub

What am I doing wrong/missing? Any help will be much appreciated.

Thanks - Grant

 

[Andy Pope]

Hi Grant,

Your code will only do the named ranges on the active sheet because this
line Range(nm).Select generates an error if the sheet is not active.

This revised code will do the formatting without using Select or Selection

Sub FormatH1()
Dim namTemp As Name
Dim strH1 As String
strH1 = "H1_Server_*"
For Each namTemp In ActiveWorkbook.Names
If namTemp.Name Like strH1 Then
With namTemp.RefersToRange.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next
End Sub

Cheers
Andy

 

[Melanie Breden]

Hi Grant,

In my workbook I have two named worksheets, "Extract" and "Info".

On the worksheet "Extract" I have three named Ranges, "H1_Server_1",
"H1_Server_2", "H1_Server_3"

On the worksheet "Extract" I have three named Ranges, "H1_Server_4",
"H1_Server_5", "H1_Server_6"

I need to fashion a script that will locate any named range starting with
"H1_Server_" on any worksheet and then change the background color of the
named ranges.

try this:

Sub FormatH1()
Const strH1 As String = "H1_Server_"
Dim nName As Name

For Each nName In ActiveWorkbook.Names
If InStr(nName.Name, strH1) Then _
Range(nName).Interior.ColorIndex = 35
Next nName
End Sub


--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Grant Reid]

Many Thanks to all who replied.

Regards - Grant