Change background color of all named ranges

[bradyboyy88]

HI,

I am trying to loop through all the cells in thisworkbook and change the background color to vbyellow. My code so far is as follows:

Sub testdata()


Dim nm As Variant


For Each nm In ThisWorkbook.Names
    Range(nm.RefersTo).Interior.Color = vbYellow
Next nm


End Sub

It draws an error at the range() function which makes me think this method will not work. Any help will be appreciated.

 

[JoeMo]

First, nm.refersto includes an equal sign which you have to eliminate to create a legal string address. Also, sometimes worksheet function errors cause Excel to generate hidden names you don't know are there.

Not sure, but see if this works for you:

Sub testdata()
Dim nm As Variant
For Each nm In ThisWorkbook.Names
    If Not nm.Name Like "_xlfn*" Then
        Range(Right(nm.RefersTo, Len(nm.RefersTo) - 1)).Interior.Color = vbYellow
    End If
Next nm
End Sub

 

[bradyboyy88]

First, nm.refersto includes an equal sign which you have to eliminate to create a legal string address. Also, sometimes worksheet function errors cause Excel to generate hidden names you don't know are there.

Not sure, but see if this works for you:

Sub testdata()
Dim nm As Variant
For Each nm In ThisWorkbook.Names
    If Not nm.Name Like "_xlfn*" Then
        Range(Right(nm.RefersTo, Len(nm.RefersTo) - 1)).Interior.Color = vbYellow
    End If
Next nm
End Sub

Thanks Joe but this code draws a run-time error 1004 Method Range of Object _ Global Fail. Any ideas? Some cells seem to highlight though then hits this error.

 

[Fluff]

How about

Sub bradyboyy()
    Dim Nme As Name
    For Each Nme In ThisWorkbook.Names
        If Nme.Visible Then
            If InStr(1, Nme, "!") > 0 Then Range(Nme).Interior.Color = vbYellow
        End If
    Next Nme
End Sub

 

[JoeMo]

When that error occurs, I assume it highlights the Range(....) line, open the Immediate Window and type:?nm.Name then press enter. If that returns a name, then type: ?nm.RefersTo and press enter. What do you get?

 

[bradyboyy88]

It looks like one of the name ranges show the value as =#REF!=#REF! which is strange. Any ideas why it would show that even though that doesnt seem to be in the named range list. Using the updated code above seems to work but I am wondering which range could cause a reference error. Does checking if its visible miss ones that are in hidden rows ?

Sub FormattingRange()
    Dim Nme As Name
    For Each Nme In ThisWorkbook.Names
        If Nme.Visible And Nme <> "=#REF!#REF!" Then
            If InStr(1, Nme, "!") > 0 Then Range(Nme).Interior.Color = vbYellow
        End If
    Next Nme
End Sub

 

[JoeMo]

It looks like one of the name ranges show the value as =#REF!=#REF! which is strange. Any ideas why it would show that even though that doesnt seem to be in the named range list. Using the updated code above seems to work but I am wondering which range could cause a reference error. Does checking if its visible miss ones that are in hidden rows ?

Sub FormattingRange()
    Dim Nme As Name
    For Each Nme In ThisWorkbook.Names
        If Nme.Visible And Nme <> "=#REF!#REF!" Then
            If InStr(1, Nme, "!") > 0 Then Range(Nme).Interior.Color = vbYellow
        End If
    Next Nme
End Sub

If you open the Name Manager you can filter for names with errors and delete them. From what you show above it seems the name was on a sheet that's no longer part of the workbook.

 

[JoeMo]

Here's my cursory attempt to combine all the knowledge imparted by the previous posts to arrive at something more robust:

Sub testdata()
Dim nm As Variant
For Each nm In ThisWorkbook.Names
    If nm.Visible And InStr(nm.RefersTo, "!") > 0 And InStr(nm.RefersTo, "#REF!") = 0 Then
        Range(nm).Interior.Color = vbYellow
    End If
Next nm
End Sub

 

[mikerickson]

How about

For Each nm in ThisWorkbook.Names
    On Error Resume Next
        nm.RefersToRange.Interior.Color = vbYellow
    On Error Goto 0
Next nm

 

[JoeMo]

How about

For Each nm in ThisWorkbook.Names
    On Error Resume Next
        nm.RefersToRange.Interior.Color = vbYellow
    On Error Goto 0
Next nm

Even better - thanks for that Mike.