this code is working well to me on my work & here i want a small change in this code any excel experts will solve this
when i am typing any unique mobile number in column B then it will showing me in msgbox Duplicate Entry & here this msgbox is correct when i type any duplicate mobile number in column B then only it will show msgbox Duplicate Entry & here is problem with this code if i type unique mobile number then also it will showing msgbox Duplicate Entry & here my request is the Msgbox will be show Duplicate Entry on if i type Duplicate Mobile number only not on Unique mobile number entry
And one more request
when i am typing 2 or 3 mobile numbers in a cell of column B with adding any special character in middle of every mobile number in that cell then also i will getting msgbox please check the number & deleting the numbers in that cell & here this msgbox also correct when if i type mobile number more than or less than 10 digits then only this msgbox will come
here my request is if i type 2 or 3 or how any mobile numbers its have it should accept with any msgbox showing
when i am typing any unique mobile number in column B then it will showing me in msgbox Duplicate Entry & here this msgbox is correct when i type any duplicate mobile number in column B then only it will show msgbox Duplicate Entry & here is problem with this code if i type unique mobile number then also it will showing msgbox Duplicate Entry & here my request is the Msgbox will be show Duplicate Entry on if i type Duplicate Mobile number only not on Unique mobile number entry
And one more request
when i am typing 2 or 3 mobile numbers in a cell of column B with adding any special character in middle of every mobile number in that cell then also i will getting msgbox please check the number & deleting the numbers in that cell & here this msgbox also correct when if i type mobile number more than or less than 10 digits then only this msgbox will come
here my request is if i type 2 or 3 or how any mobile numbers its have it should accept with any msgbox showing
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Application
On Error Resume Next
If Not Intersect(Target, Range("B1:B1000")) Is Nothing And Target.Count = 1 And Len(Target) <> 10 Then
.EnableEvents = False
MsgBox "Please check the number you have Enter The Mobile should be contain 10 Digits only"
.Undo
.EnableEvents = True
Exit Sub
Else
a = Application.Match(Target.Value, Range("B1:B1000"), 0)
If IsNumeric(a) Then
If MsgBox("You have Entered the Mobile Number is Already Exist in cell " & Cells(a, 2).Address(0, 0) & vbNewLine & "If you want to continue with Duplicate Mobile Number click (YES)" _
& vbNewLine & "If want to remove Duplicate Mobile Number in EnireRow click (NO)", _
vbQuestion + vbYesNo + vbDefaultButton2, "Duplicate Entry") = vbNo Then
Target.Cells.EntireRow.Delete
.EnableEvents = False
.Undo
.EnableEvents = True
End If
End If
End If
End With
End Sub