open excel the press alt+f11 then dbclick on required sheet and paste the following code private sub worksheet_change(byval target as range) dim targ as range, cel as range, rg as range set targ = [d:d] 'change to the cell range being watched for duplicates set targ = intersect(targ, target) if targ is nothing then exit sub for each cel in targ.cells if cel <> "" then set rg = columns(cel.column).find(cel, lookin:=xlvalues, after:=cel) if rg.address <> cel.address then application.enableevents = false cel = "" cel.select application.enableevents = true msgbox "duplicate entry! " & cel.value end if end if next end sub