How to highlight all named ranges in Excel?

Named ranges in Excel provide a convenient way to reference specific cells or ranges using meaningful names instead of cell addresses. However, if your spreadsheet contains multiple named ranges, locating them all can be a challenge. This article outlines several efficient methods to highlight all named ranges in Excel.

Highlight all named ranges in a workbook with VBA code

Highlight all named ranges in a workbook with Kutools for Excel

Highlight all named ranges in a workbook with VBA code

Here is a code to highlight all the named ranges in a workbook, you can do as this:

1. Hold down the "ALT + F11" keys, and it opens the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code in the Module Window.

VBA code: Highlight all named ranges in Excel

Sub HighlightRange()
'Updateby Extendoffice
Dim xName As Name
Dim Rng As Range
On Error Resume Next
For Each xName In Application.ActiveWorkbook.Names
    Set Rng = xName.RefersToRange
    Rng.Interior.ColorIndex = 6
Next
End Sub

3. Then press F5 key to execute this code, and all the named ranges have been highlighted with yellow color in the whole workbook.

Highlight all named ranges in a workbook with Kutools for Excel

Kutools for Excel offers a powerful "Highlight Names" feature that allows users to quickly identify all named ranges dynamically. Unlike traditional methods, this feature automatically updates the highlighting when new named ranges are added or existing ones are removed, ensuring real-time visibility.

After installing Kutools for Excel, please do as this:

1. Click "Kutools Plus" > "Design View", see screenshot:

2. In the opened "Kutools Design" tab, Click "Highlight Names", see screenshot:

Now, all named ranges in the entire workbook are highlighted simultaneously. If new named ranges are added or existing ones are removed, the highlighting updates dynamically to reflect these changes.

By using VBA code or the Kutools for Excel tool, users can easily highlight all named ranges in a workbook. Regardless of the chosen method, both tools effectively help users quickly identify and manage named ranges in a workbook, enhancing work efficiency and data readability. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.

Related articles:

How to highlight all subtotal rows at once in Excel?

How to highlight all error cells in Excel?

How to highlight cells with external links in Excel?