Hi Excellers and welcome back to another #macromonday blog post in my 2019 series. Today I want to show you how to write a really quick and simple Excel VBA macro to highlight all named ranges. This macro is really useful if you are not sure how many named ranges you have in your Excel worksheet.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or 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 either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro 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.
Starting The Macro
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.
The next step in writing our macro is to declare some variables. This ensures that Excel creates a memory container for these values. In this example, there are two variables to declare.
Using For Each
Loops in Excel VBA
Next, we instruct Excel to use the For Each Looping command. We start with For Each and then need a variable name. 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 color. The final part of the loop is “Next”.
This looping continues until all of the named ranges have been highlighted with color in the workbook.
Ending The Macro
Once all of the named ranges have been filled with color the codes then end with the “End Sub” piece of code. This was already entered into the module for us when started the type the name of the macro.
More Related #MacroMonday Blog Posts
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below