Prohibit duplicate data in a cell

[Rich]

I need to prohibit the use of duplicate part numbers in a certain range of
cells.

Using the worksheet_change event, I create an array of all existing part
numbers. I can then go thru that array and compare it with the value of the
cell that the user just typed a part number into.

If I find that the part number that the user just typed in is a duplicate, I
don't want to allow the focus to shift to another cell, and I want the cell
value to be highlighted....similar to "data validation" where the data won't
be accepted into the cell if it is invalid. I'm not sure how to do this part
of the task.

Can anyone point me in the right direction?? TIA.

 

[Chip Pearson]

You can use Data Validation to do this. Suppose you range to test is
A1:A10. Select that range, open the Data Validation dialog, choose
"Custom" from the Allow list, and use the following formula as the
valdiation formula. If the formula returns TRUE, input is allowed. If
the formula returns FALSE, input is prohibited.

=COUNTIF($A$1:$A$10,A2)=1

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com