search range for duplicates
 

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!!

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!!
.

search range for duplicates
 

You can remove the "If .Count = 1" line and one of
the "End If lines near the end.

Regards,
Greg


-----Original Message-----
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!!
.

.

search range for duplicates
 

Doug,

Try the FIND METHOD. Here's a description from VBA HELP:
-----------
Find Method

Finds specific information in a range, and returns a Range object that
represents the first cell where that information is found. Returns Nothing
if no match is found. Doesn't affect the selection or the active cell.
For information about using the Find worksheet function in Visual Basic, see
Using Worksheet Functions in Visual Basic.
Syntax
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte)

TH

On 4/15/04 18:33, in article , "Doug
Loewen" wrote:

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!!