Macro for preventing of entering duplicates in column M

Invalid post - ignore

are you sure?

The command is incomplete.

I am really sorry posted that by mistake.
Select column A.
Goto DATA >> DATA VALIDATION >> CUSTOM
Enter this formula- =CountIf($A$1:$A1,$A1)=1

You can change the error message as per your requirement.
Also this would work only for typed values. So if you copy values from other cells this wont work.

Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim EvalRange As Range
    
Set EvalRange = Range("M4:M10000")

If Intersect(Target, EvalRange) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If WorksheetFunction.CountIf(EvalRange, Target.Value) > 1 Then
    MsgBox Target.Value & " has already been used." & vbCr & "Do you want to use the same number (cancel) or enter a new one (retry)?", vbInformation, "Value Already Exist"
    
    Application.EnableEvents = False
        Target.Value = ""
    Application.EnableEvents = True
End If

End Sub

Hi bajramo,

If your problem is solved then mark the thread solved.