Jump to duplicat entries

[Guest]

I have an application where a row is designed to allow a user to put in various product requirements for a product. Column B difines the product code. Because this must be a unique record, a product code can only be used once in the spreadsheet. I'm looking for a way to check for duplicate product codes, and if a duplicate es entered (E.g.: cell B100), Excel would jump to the existing product code (E.g.: B5) to allow further entries within the row

Any ideas

Thanks in advance for your help.

 

[mudraker]

This needs to be placed on the module sheet for the spreadsheet tha
holds your data eg sheet1 Module



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TargetAddress

Dim Cell As Range
If Target.Column = "B" Then
'action only if change made in product code column
For Each Cell In Range("B1:B" & UsedRange.Count)
' check entered value against all entries in producyt code
If Cell.Address <> Target.Address Then
' do not compare target and cell address are the same
If Cell.Value = Target.Value Then
Cell.Select

Target.clear ' clears new entry form column B
or
Target.Rows.Clear ' clears new entry row
Exit For
End If
End If
Next Cell
End If
End Su