I hope @Sintek doesn't complain my intruding again, but you may combine anything in the Worksheet Change, it's time you try and follow some tutorials and try it out, THINK like the code it's nothing more, nothing less than executing one line of code at the time.
You tell is if this then do that else do something else.
I combined it for you and it works with Sintek's file and perfect solution
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ListObjects(1).ListColumns(5).DataBodyRange) Is Nothing Then
Application.EnableEvents = False
Target.NumberFormat = "@"
Application.EnableEvents = True
ElseIf Not Intersect(Target, ListObjects(1).ListColumns(4).DataBodyRange) Is Nothing _
And Target.Cells.Count = 1 Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Value = USPhone(Target.Value)
If InStr(Target.Value, "-") = 0 Then
Target.Font.Color = vbRed
Else
Target.Font.ColorIndex = xlAutomatic
End If
End If
ErrHandler:
Err.Clear
On Error GoTo 0
Application.EnableEvents = True
End Sub
Public Function USPhone(s As String) As String
Dim t As String
Dim l As Long
For l = 1 To Len(s)
If Asc(Mid$(s, l, 1)) > 47 And Asc(Mid$(s, l, 1)) < 58 Then
t = t & (Mid(s, l, 1))
End If
Next
If Len(t) = 10 Then
USPhone = Format$(t, "(###) ###-####")
Else
USPhone = t
End If
End Function
Bookmarks