Find a duplicate number in cell range and displaying a message box.

[jon124]

Hello,

I have a list of cells all in the same column(33 of them). Individuals have to rank each cell that corresponds to a task beside the cell. I need to check the range each time the individual enters a value from 1-33 on each cell that there is no duplicates that exist. If there is, then a Message box would appear stating "Number already Used", with just "OK" from the Message box, but not letting individual go to another cell until condition is cleared.
Any help would be greatly appreciated.

Chris Jones
[email protected]

[mrice]

You can get something close to this by simply using conditional formatting to highlight duplicates. It doesn't stop them being created but makes it very obvious when they have.

[Alf]

Or you could try a "Private Sub Worksheet_Change" macro

Please Login or Register  to view this content.

This works for values entered in the B column, right click on the sheet tab click "View Code" and paste the code in the new windows that opens.

Alf