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

Private Sub Worksheet_Change(ByVal Target As Range)
'Define variables.
    Dim ws As Worksheet, wkb As Workbook, EvalRange As Range
    Set wkb = ActiveWorkbook
    
    Set ws = wkb.Worksheets("Orders")
    Set Target = Range("a4")
    Target.NumberFormat = "0000"
   
    'Set the range where I want to prevent duplicate entries.
    Set EvalRange = Range("a4:a10000")
    
    'If the cell where value was entered is not in the defined range, if the value pasted is larger than a single cell,
    'or if no value was entered in the cell, then exit the macro.
    If Intersect(Target, EvalRange) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    If IsEmpty(Target) Then Exit Sub
    
    'If the value entered already exists in the defined range on the current worksheet, throw an
    'error message and undo the entry.
    If WorksheetFunction.CountIf(EvalRange, Target.Value) > 1 Then
        If MsgBox(Target.Value & " has already been used." & vbCr & "Do you want to use the same number (cancel) or enter a new one (retry)?", vbRetryCancel) = vbRetry Then
        Application.EnableEvents = False
        
        Application.EnableEvents = True
            Range("A4").Value = ""
            Range("A4").Select
        End If
    End If
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$4" Then
   Call checkRepeat
 End If
End Sub

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

 Sub checkRepeat()
 

 Dim ws As Worksheet, wkb As Workbook, EvalRange As Range
    Set wkb = ActiveWorkbook

    Set ws = wkb.Worksheets("Orders")
    Set Target = Range("a4")
    Target.NumberFormat = "0000"

    'Set the range where I want to prevent duplicate entries.
    Set EvalRange = Range("a4:a10000")
   MsgBox Target


    'If the cell where value was entered is not in the defined range, if the value pasted is larger than a single cell,
    'or if no value was entered in the cell, then exit the macro.
   If Intersect(Target, EvalRange) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
   If IsEmpty(Target) Then Exit Sub

    'If the value entered already exists in the defined range on the current worksheet, throw an
    'error message and undo the entry.
    If WorksheetFunction.CountIf(EvalRange, Target.Value) > 1 Then
        If MsgBox(Target.Value & " has already been used." & vbCr & "Do you want to use the same number (cancel) or enter a new one (retry)?", vbRetryCancel) = vbRetry Then
        Application.EnableEvents = False
       '
       Application.EnableEvents = True
            Range("A4").Value = ""
            Range("A4").Select
          End If
        End If
 End Sub

The old fogey