ExecuteExcel4Macro Conditional Formatting - Phone Number Format

[Trag]

Hi all,

I tried searching the form to see if I could find an issue similar to mine - I came up empty.

I'm trying to apply the phone number format ([<=9999999]###-####;(###) ###-####) to each phone number cell in column D if the cell in column N is equal to "United States of America".

Here is the macro I've come up with;

Sub Format_USA_PhoneNumbers()

Columns("D:D").Select
Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$N$1=""United States of America"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
ExecuteExcel4Macro "(2,1,""[<=9999999]###-####;(###) ###-####"")"
Selection.FormatConditions(1).StopIfTrue = False

End Sub

Example of what I'm looking to do:

If N35 = "United States of America"
D35 format is (###) ###-####

If N99 = "Canada"
D99 = 0

If N106 = "United Kingdom"
D106 = 0

If N888 = "United States of America"
D888 format is (###) ###-####


The problem I'm having is that every time I run the macro, I receive an error;
Run-Time Erro '1004';

The formula you typed contains an error. When I debug it, it highlights this line;

ExecuteExcel4Macro "(2,1,""[<=9999999]###-####;(###) ###-####"")"

Can someone please assist with a recommendation / solution to what I'm trying to achieve? I'd very much appreciate it!

Using Excel 2010

 

[Scott Huish]

If you are using Excel 2010, why not just use Conditional Formatting? It supports number formats.

 

[Trag]

Hi Scott,

I'm trying to use conditional formatting via a macro so that the process is automated. I'm using another macro to call this one as well as a few others so I'm not keen on having to take manual steps. I'd prefer the macro be self sufficient and not rely on manual intervention.

 

[Trag]

I'm also not opposed to doing a loop - just not sure how to get it the 'with' portion to work...

Sub Format_USA_Phone_Alt1()

Columns("N:N").Select

For Each MyCell In Selection
If MyCell.Value Like "United States of America" Then
With
'I'm not sure what I need to do here...


End With
End If
Next
End Sub

 

[Trag]

Figured it out... - used a case statement with offset.

 

[Scott Huish]

How about this?

Sub Test()
With Columns("D:D")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$N1=""United States of America"""
    .FormatConditions(1).NumberFormat = "[<=9999999]###-####;(###) ###-####"
    .FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
    .FormatConditions(2).NumberFormat = "0"
End With
End Sub

 

[Trag]

That works perfectly too! Thanks Scott!