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
Post a Comment