Hi there again. Not too sure if this is going to be possible. I have created
a fairly useful macro that creates a list of range names in the main workbook
that I use (current count is 1471 names). The macro then runs through my
eleven workbooks that each refer back to the main workbook, and it checks for
references to the 1471 named cells in the main book, lists them and
conditional formats the cell names that are not referenced to show a bright
colour.

The money part of this macro is


For Each Sh In lk_bk.Worksheets
If Sh.Visible = False Then: Sh.Visible = True: Sh.Select
cnt = 2
Do While cnt <= 1470 '????????????
lk_nm = Tsh.Cells(cnt, 3).Value
With Sh.UsedRange
Set C = .Find(lk_nm, LookIn:=xlFormulas)
If Not C Is Nothing Then
firstAddress = C.Address
Do
Tsh.Cells(cnt, cl).Value = "Y": Exit Do
If Err.Number = 91 Then: Err.Clear
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <
firstAddress
End If
End With
cnt = cnt + 1
Loop
Next Sh


and later on I set the conditional fomats.

This gives me a list that I can then use to delete all the names that are no
longer used.

HOWEVER, before I can delete them I need to run through the PERSONAL.xls
project in the same way, checking for references to the named cells (from the
list of 1471) and flagging them in the same conditional formatted way. Can
this be done? I couldn't find any references on Chip Pearson's excellent &
generous website. Regards, Brett.

HOLD THE PHONE.......I found what I need on Chip's site after all

"Brettjg" wrote:

Hi there again. Not too sure if this is going to be possible. I have created
a fairly useful macro that creates a list of range names in the main workbook
that I use (current count is 1471 names). The macro then runs through my
eleven workbooks that each refer back to the main workbook, and it checks for
references to the 1471 named cells in the main book, lists them and
conditional formats the cell names that are not referenced to show a bright
colour.

The money part of this macro is


For Each Sh In lk_bk.Worksheets
If Sh.Visible = False Then: Sh.Visible = True: Sh.Select
cnt = 2
Do While cnt <= 1470 '????????????
lk_nm = Tsh.Cells(cnt, 3).Value
With Sh.UsedRange
Set C = .Find(lk_nm, LookIn:=xlFormulas)
If Not C Is Nothing Then
firstAddress = C.Address
Do
Tsh.Cells(cnt, cl).Value = "Y": Exit Do
If Err.Number = 91 Then: Err.Clear
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <
firstAddress
End If
End With
cnt = cnt + 1
Loop
Next Sh


and later on I set the conditional fomats.

This gives me a list that I can then use to delete all the names that are no
longer used.

HOWEVER, before I can delete them I need to run through the PERSONAL.xls
project in the same way, checking for references to the named cells (from the
list of 1471) and flagging them in the same conditional formatted way. Can
this be done? I couldn't find any references on Chip Pearson's excellent &
generous website. Regards, Brett.