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