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.

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com