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


Grant Reid wrote:

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, Microsoft MVP - Excel
http://www.andypope.info