write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Saturday, 11 February 2012

Excel : Find duplicate entry while entering data in column

For any column in worksheet .This will give you alert while entering duplicate entry with msg box & column header name. highlight the duplicate entry

Right click on sheet tab-> view code->copy below code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Application.WorksheetFunction.CountIf(Range(Cells(2, Target.Column), Cells(Target.Row, Target.Column)), Target) > 1 Then
     Alert = MsgBox(Cells(1, Target.Column) & " Already Exist - Click Yes do delete", vbYesNo)
     If Alert = vbYes Then
     Target.Interior.ColorIndex = 6
     Exit Sub
     End If
End If
End Sub

No comments:

Post a Comment