Named ranges are very useful when creating Excel models. However, it can get pretty hard to track what named range belongs where as your model grows. Highlighting them on your worksheets can help you identify their properties easily. In this article, we're going to show you how to find named ranges in Excel by highlighting them using VBA.

How to find named ranges in Excel by highlighting them using VBA

Named ranges are objects in the Names collection, which is an element of a workbook. You can loop through the existing names using a For…Next loop. Name items can return their ranges using the RefersToRange method. After you get the range, you can use the ColorIndex property to set that range a color. Here are some index numbers for colors:

• 3: Red
• 5: Blue
• 6: Yellow
• 0: No Fill

We suggest you to use On Error Resume Next line at the start of your code in case of any erroneous named range.

First, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

Highlight named ranges

Sub HighlightNamedRanges()

    On Error Resume Next

    Dim nm As Name

    For Each nm In ActiveWorkbook.Names

        nm.RefersToRange.Interior.ColorIndex = 3 '3: Red, 6:Yellow

    Next nm

End Sub