If you spend your time auditing other people's worksheets, you'll know that Excel users love their named ranges. I've run across some spreadsheets where 50 or more individual cells are given individual names. This makes auditing a spreadsheet an extremely muddy experience.
It sometimes helps to know where the named ranges are.
.
Here is a macro I use to highlight all the named ranges in a workbook yellow.
Just copy and paste this code in a new Standard Module. Once you have it in, you can fire this macro to turn all the cells that belong to a named range Yellow.
Dim RangeName As Name
Dim HighlightRange As Range
On Error Resume Next
For Each RangeName In ActiveWorkbook.Names
Set HighlightRange = RangeName.RefersToRange
HighlightRange.Interior.ColorIndex = 36
Next RangeName
End Sub
.
As a bonus, this tip also gives you a visual indication of the level of anal retentiveness your co-workers achieve.
Next week, I'll give you a way to indicate to which Named range each highlighted cell belongs.
Good suggestion
But since "Named Ranges" can be defined as constants or formulas that do not result in a range on any worksheet (for example, =c5+d5, and in infinite number of others), the subroutine needs a:
On Error Resume Next
...to prevent it from crashing.
Regards,
Daniel Ferry
excelhero.com/blog
Daniel: Very good point! I added the error catch.
What about putting the hilites back to normal (whatever was there before you colored them)?
I have a similar routine that hilites locked cells which uses select instead of color index.
Alexj: If you don't want to color, but just select the named ranges, you can use this:
Dim RangeName As Name
Dim HighlightRange As Range
On Error Resume Next
For Each RangeName In ActiveWorkbook.Names
If HighlightRange Is Nothing Then
Set HighlightRange = RangeName.RefersToRange
Else
Set HighlightRange = Union(HighlightRange, RangeName.RefersToRange)
End If
Next RangeName
HighlightRange.Select
End Sub
why not just use this:
Range(RangeName).Interior.ColorIndex = 36
The real Name Manager has this functionality already...
Same, you beat me to it, and you used my phrasing