Macro 34: Select and Format All Named Ranges
If you spend your time auditing other people’s worksheets, you’ll know that Excel users love their
named ranges. It’s not uncommon to encounter spreadsheets where dozens of cells and ranges are
given individual names. This makes auditing a spreadsheet an extremely muddy experience. It some-
times helps to know where the named ranges are. Here is a macro you can use to color all of the
named ranges in a workbook yellow.
How it works
In this macro, we are looping through the Names collection of the active workbook to capture each
named range. When a named range is captured, we color the range.
Sub Macro34()
‘Step 1: Declare your variables.
Dim RangeName As Name
Dim HighlightRange As Range
‘Step 2: Tell Excel to Continue if Error.
On Error Resume Next
‘Step 3: Loop through each Named Range.
For Each RangeName In ActiveWorkbook.Names
‘Step 4: Capture the RefersToRange
Set HighlightRange = RangeName.RefersToRange
‘Step 5: Color the Range
HighlightRange.Interior.ColorIndex = 36
‘Step 6: Loop back around to get the next range
Next RangeName
End Sub
1. We first declare two object variables. The first variable called RangeName holds each named
range as the macro enumerates through the Names collection. The second variable called
HighlightRange captures the range to which RangeName is referring.
2. Technically, an Excel user can assign a “name” to things that are not actually a range (such as
constants or formulas). So with that in mind, we have to anticipate that Excel will throw an
error if the RefersToRange property of the named range does not represent a range
address. In this step, we tell Excel to ignore any error that is thrown and continue to the next
line of code. This ensures that the code doesn’t abruptly stop due to a bad range address.
3. In this step, the macro starts looping through each name in the active workbooks Names
collection.
4. After a named range is activated, the macro captures the address in our HighlightRange
object variable. This exposes all the properties we can use to format the range.
5. In Step 5, we assign a color to the cells in the captured range.
6. Finally, we loop back to get the next named range. The macro ends after we have
enumerated through all of the names.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it will be named personal.xlb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
If you don’t see personal.xlb in your project window, it doesn’t exist yet. You’ll have to record a
macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In
drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the
recorded macro and replace it with this one.
