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.
-
Sub HighlightRanges()
-
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.
RELATED STUFF
- Choosing Quarters in Excel
- Creating Subtotals on Many Columns
- Protect Worksheet Structure with Array Formulas
- Using the Switch Function in Excel
- Protecting VLOOKUPS from Expanding Data
- Copying VLOOKUPs Across Multiple Columns
- Flipping a Range Upside Down
- Excel Defunct Defaults
- Why FIND when you can SEARCH
- Getting Data from Previous Sheet
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:
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