Excel VBA Phone Number Format Issue

[AnnAnn]

Hi Everyone,
I've searched Google, YouTube, and this site but didn't find what I needed. Any help is appreciated.
I have a macro on a workbook that runs on another open workbook; all code is based on the column name using a FindColumn sub.
This code needs to highlight cells in the column if they're blank, or not in ###-###-####, (###)###-####, or ########## format however it highlights cells in (###)###-#### format and I can't figure out why.
Anyone have any ideas or a better code I can use?

Sub PhoneNbr(ws As Worksheet, lastCol As Long, lastRow As Long)

Dim rng As Range, cell As Range
Dim word As Variant
Dim colLtr As String

colLtr = FindColumn(ws, "Phone Number", 2)

If colLtr = "Null" And lastRow > 2 Then

    Else
    
    
    Set rng = ws.Range(colLtr & "3:" & colLtr & CStr(lastRow))
    
    rng.FormatConditions.Delete
    
    For Each cell In rng
            If cell Like "###-###-####" Or cell Like "##########" Or cell Like Trim("(###)###-####") Then
                    cell.Interior.Color = xlNone
                   Else
                    cell.Interior.Color = RGB(255, 204, 0)
                     With ws.Range(colLtr & "2")
                            .Interior.Color = RGB(0, 0, 0)
                            .Font.Color = RGB(255, 255, 255)
                     End With
            End If
    Next cell
End If
End Sub

 

[Scott Huish]

Since you're running a macro anyway, why not use the macro to convert the data to the format you need?

 

[AnnAnn]

Since you're running a macro anyway, why not use the macro to convert the data to the format you need?

Hi Scott, Because this sub is part of a main macro that has 74 other subs and it's already taking 2mins to complete on a 500 row file. Adding code to reformat adds too much time.

 

[AnnAnn]

Hi Scott, Because this sub is part of a main macro that has 74 other subs and it's already taking 2mins to complete on a 500 row file. Adding code to reformat adds too much time.

I think the issue is where I've placed the Trim part. I'm new to VBA so not sure though.

 

[AnnAnn]

I think the issue is where I've placed the Trim part. I'm new to VBA so not sure though.

Thanks, Scott, for the suggestion to reformat the values. I can't remember what I tried before that was adding so much time to the macro, but I've updated the code to below and it only added 10 seconds. Thanks again!

Sub PhoneNbr(ws As Worksheet, lastCol As Long, lastRow As Long)

Dim rng As Range, cell As Range
Dim word As Variant
Dim colLtr As String

colLtr = FindColumn(ws, "Phone Number", 2)

If colLtr = "Null" And lastRow > 2 Then

    Else
    
    
    Set rng = ws.Range(colLtr & "3:" & colLtr & CStr(lastRow))
    rng.Replace What:="(", Replacement:=""
    rng.Replace What:=")", Replacement:="-"
    rng.Replace What:=" ", Replacement:=""
    
    rng.FormatConditions.Delete
    
    For Each cell In rng
    
            If cell Like Trim("###-###-####") Or cell Like Trim("##########") Then
                    cell.Interior.Color = xlNone
                   Else
                    cell.Interior.Color = RGB(255, 204, 0)
                     With ws.Range(colLtr & "2")
                            .Interior.Color = RGB(0, 0, 0)
                            .Font.Color = RGB(255, 255, 255)
                     End With
            End If
    Next cell
End If
End Sub