You will see that your added requirement increases the code require
quite a bit :-

'--------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim r As String
Dim Rng As Range
'----------------------
If Target.Column = 1 Then
'- find target range
For Each nm In ActiveWorkbook.Names
r = (Right(nm.RefersTo, Len(nm.RefersTo) - 1))
r = Mid(r, InStr(1, r, "!") + 1, 255)
Set Rng = ActiveSheet.Range(r)
If Not Intersect(Target, Rng) Is Nothing Then
Exit For
End If
Next
'- check for duplicate
If Application.WorksheetFunction. _
CountIf(Rng, Target.Value) 1 Then
Target.Interior.ColorIndex = 6
MsgBox ("Name already exists.")
Target.Interior.ColorIndex = xlNone
Target.Value = ""
End If
End If
End Sub
'------------------------------------------------------------

--
Message posted from http://www.ExcelForum.com