search range for duplicates
Paste the following to the sheet code module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
With Target
Set Rng = Columns(.Column)
If .Count = 1 Then
If Application.CountIf(Rng, .Value) 1 Then
..Select
..Interior.ColorIndex = 6
MsgBox "Name already exists"
..Interior.ColorIndex = xlNone
End If
End If
End With
End Sub
Regards,
Greg
-----Original Message-----
I have 5 ranges (columns) Mon Tue Wed etc. I enter names
in each range. As each name is entered I would like to
search the names already entered above it and check for
duplicates, then have a message box "Name already used"
It's OK if I duplicate names from Mon to Tue so I want to
check each day separately. Any ideas? Thanks!!
.
|