HERE I WANT A SMALL CHANGE IN THIS CODE AGAIN

[Prasad K]

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

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

 

[DanteAmor]

Hi Prasad K:

It is not necessary that you reply the whole answer, just select the text or a part of the text and press the Rreply button, it is a matter of form, to know exactly what you are replying to and not to make each answer huge.

can you change this into like this
Msgbox You have Entered the Mobile Number '8045791489' is Already Exist in Cell B4 For 'Talwar Hyundai Pvt Ltd'

Change this:

                  If MsgBox("You have Entered the Mobile Number '" & mbl & _
                     "' is Already Exist in cell " & f.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

To this:

  If MsgBox("You have Entered the Mobile Number '" & mbl & "' " & _
     "is Already Exist in cell " & f.Address(0, 0) & " " & _
     "For " & f.Offset(0, -1) & 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