Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

Formatting phone numbers macro using to check condition

Change cell color if the content does not adhere to a specific phone number format. I am trying to check for phone number Formats like +44 (0) 1224561918

If the condition is not met the cell should be highlighted by a different color to represent an error.

Sub FormatPhoneNumber()
    Dim sJustNumber As String 
    Dim sPhoneNumber As String
    Dim iCtr As Integer  
    Dim Target As Range 

    If Intersect(Target, Range("AB:AC")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub

    Application.EnableEvents = False

    For iCtr = 1 To Len(Target.Value)
        If IsNumeric(Mid(Target.Value, iCtr, 1)) Then        
            sJustNumber = sJustNumber & Mid(Target.Value, iCtr, 1)
        End If
    Next iCtr

    If Len(Trim(sJustNumber)) < 10 Then
        MsgBox "The value in this cell does not appear to be " & _
               "a full valid phone number that includes the Area Code. " & _
               Chr(13) & "Please edit manually.", vbApplicationModal + _
               vbInformation + vbOKOnly, "Manual Editing Required"
        GoTo Finish    'Exit without changing cell value
    End If

    If Len(sJustNumber) = 10 Then
        sPhoneNumber = "+44 (0) " & sPhoneNumber
    End If

    Target.Value = sPhoneNumber

Finish:
    Application.EnableEvents = True
End Sub

Above condition is work. But not check the length & Correct Values. I want this format. +44 (0) 1224561918. But it shows Below.

Correct format: +44 (0) 12345677891 (should be 10 digits(1-9))

Wrong format : +44 (0)  1234567891 (no double space or more spaces)
               +44Singlespace(0)singlespace1234567891
+44 (0) 1234567891(inside the braces "0" only) not like +44 (1-9) 1234567891

Check Length And condition.

Comments