Hi Excellers, and welcome back to another #Excel #MacroMonday blog post in my 2019 series. Today I want to show you how to write a quick and straightforward Excel VBA macro to highlight all named ranges in Excel. This macro is handy if you are not sure if you have named ranges in your Excel workbook or how many. In addition, it is advantageous if you have inherited an Excel workbook and must work out its structure to understand it quickly.
Tutorial Contents.
Preparing To Write The Code To Highlight Named Ranges.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11
- Selecting the Developer Tab | Code group | Visual Basic
Both methods have the same result. You then have a choice. You can either create a module to store your code in your Personal Macro Workbook or your current workbook. What’s the difference?. If you save the code in your Personal Macro workbook, it will be available for use in any of my Excel workbooks. If you store it in the current workbook, then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW, then check out my blog posts below.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro. Find Named Ranges.
We need to start the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called the macro HighlightNamedRanges. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. Next, we need to enter the rest of the code between these two lines.
Sub HighlightNamedRanges
End Sub
Declaring Variables.
The next step is to declare any variables. Variables ensure that Excel creates a memory container for these values. In this example, there are two variables.
Dim RangeName As Name
Dim HighlightRange As Range
Using For Each Loop in Excel VBA.
Next, instruct Excel to use the For Each Looping command. In this routine, start with For Each and then a variable name follows. In this example, it is RangeName. VBA will store individual items into this variable. After the variable name, we use the word “In". Finally, follow that with the name of the collection or array you’re trying to access. In this example, it is ActiveWorkbook.Names.
We then instruct Excel to highlight the Named Ranges in the Active Workbook and then fill the cells with a specified colour. The final part of the loop is “Next".
This looping continues until all of the named ranges become highlighted with colour in the workbook.
We then instruct Excel to highlight the Named Ranges in the Active Workbook and then fill the cells with a specified colour. The final part of the loop is “Next”.
This looping continues until all of the named ranges have been highlighted with colour in the workbook.
For Each RangeName In ActiveWorkbook.Names
Set HighlightRange = RangeName.RefersToRange
HighlightRange.Interior.ColorIndex = 36
Next RangeName
Ending The Excel Macro.
finally, the macro is finished with “End Sub”, with all named ranges highlighted. Notice, the final line of code is already present from when the user began writing the macro.
End Sub
Copy The Code To Highlight Named Ranges In Excel.
Further Recommended Reading.
Recommended Excel Courses.
