If number in column duplicated on sheet change then problen

Hi
I have a worksheet "Orders". A macro adds a new row and increments an "OrderNO" by 1. This works fine

I have to allow the user to re-use a previous number but want to warn them it is a duplicate. I have tried using a sheet change action. If the user overtypes the automatically created number it checks for duplicates and if found a message box appears advising number already used retry or cancel.

It works fine. If retry cell is emptied and selected. If cancel then repeat number stays.

Problem is as I move to the next column it repeats the check and I get the message again. It does this for all cells in that row.

I only want it to check if A4 is reeapted in A5:A10000.

How do I stop it working from all cell changes?

Here's my code (I hope I have posted it correctly). Any help much appreciated

Please Login or Register  to view this content.

The old fogey

Thanks SixthSense. That works in that it sets the cell to empty however I need to allow them to re-use a number 2 or more times if they want to. I was trying to do it by using vbretry / cancel. If cancel then allow the number to stay as a duplicate.

My code did that but when I went into another cell eg D4 and typed soemthing in then left the cell the check on A5:A1000 fires off again. It does it for every cell I change in that row.

I need to stop the check once the focus leaves A4

Cheers
the old fogey

HI Sixth sense

I think I have it solved.

In my worksheet change event I set the target to just A4

Please Login or Register  to view this content.

Then I created a new macro witht he check for repeat code
Now it doesnt fire off when in a different cell.

Heres my macro code to do the check and allow repeat if cancel is selected

Please Login or Register  to view this content.

The old fogey