Last week, I posted 'Highlight All Named Ranges Part 1', where I shared a macro that will literally color all the cells that belong to any Named Range yellow.
This allows you to get a visual indication of how many Named Ranges exist and where they are.
For example, in this screenshot I've run my highlight ranges code. You can see there are four Named Ranges here.
This is pretty cool, but now I'd like to figure out exactly which Named Range each cell belongs to?
That is to say, I can now see that cell F3 belongs to a Named Range, but how do I figure out which range it belongs to?
Ideally, I'd like to double-click on Cell F3 and see the name of the Named Range.
This is where this brilliant piece of code comes in. This procedure will check the range of the double-clicked cell to see if it intersects with any of the existing Named Ranges. Then it returns an answer in the form of a message box.
-
Dim oName As Name
-
Dim sList As String
-
-
'Check the active cell against each Named Range
-
For Each oName In ActiveWorkbook.Names
-
-
'If the active cell intersects with a Named Range, capture the Name
-
If Not Intersect(ActiveCell, Range(oName.RefersTo)) Is Nothing Then
-
If sList = "" Then
-
sList = oName.Name
-
Else
-
sList = sList & vbCrLf & oName.Name
-
End If
-
End If
-
Next oName
-
-
'Create a message if no Named Ranges were found
-
If sList = "" Then
-
MsgBox "This cell is not referenced in any Named Ranges"
-
Else
-
'If Named Ranges were found, show them in a message
-
MsgBox sList, vbOKOnly, "Referenced in: "
-
End If
To implement this as a double-click, simply paste the code into the BeforeDoubleClick event of the target worksheet.
With this code in place, I can now double click on Cell F3 and see exactly which Named Range It belongs to.
In this case, it looks like F3 belongs to the Data2009 Named Range.
If the active cell belongs to several named ranges, then they will all be listed.
So this piece of code, coupled with the code in 'Highlight All Named Ranges Part 1' makes for a pretty handy spreadsheet auditing combo.
Ok then – I gotta run. It's 5am here, and the kids are still sleeping. It's time to steal more candy from their Easter Baskets.
RELATED STUFF
- Mocking the ‘Merge & Center’ Icon
- Highlighting the Active Row and Column
- Protect Worksheet Structure with Array Formulas
- Using the Switch Function in Excel
- Why Replace the DOM Interface
- Running an Access Parameter Query from Excel
- Fun with Custom Lists
- Excel Defunct Defaults
- Why FIND when you can SEARCH
- Getting Data from Previous Sheet
datapig,
I've been doing something similar with code borrowed from Rob Bovey's ExcelUtilities 2.1.01.
I added a list box instead of a message, and allow the user to select one of the named ranges found. This highlights the full selected range. (Rob's code is the kool bit, though).
Of course the Name Manager, By Charles Williams and myself ,has this capability built-in.
http://www.jkp-ads.com/officemarketplacenm-en.asp
Off topic and maybe a dumb question but how do you change the colour of the code in the VBA editor so that it becomes easier to read (as you show in your code examples)?
@m-b: The VB Editor already assigns colors by default; however, you can change them by clicking Tools/Options on the VB Editor's menu bar and then selecting the Editor Format tab... pick your editor items one at a time and change the colors as desired.
@Rick: thanks, I didn't know about that. Are those settings saved somewhere so you can 'transport' them between computers?
@m-b... I don't know for sure, but my gut instinct is that, no, they are not saved somewhere that you can get to easily enough to transfer between computer (my bet is that they are stored in the registry somewhere).