Give currency format to a cell value in vba

[YasserKhalil]

Hello everyone
I have a table and I need to format the fifth column within that table to be currency (dollar) and I used this line

Code: Select all

.ListColumns(5).DataBodyRange.NumberFormat = "$#,##0.00"

It works but formats the column as local currency, not the dollar currency. Any idea how to fix that?

[YasserKhalil]

I have searched and found a workaround like that

Code: Select all

.ListColumns(5).DataBodyRange.NumberFormat = "[$$-en-US]#,##0"

[HansV]

Alternatively:

Code: Select all

.ListColumns(5).DataBodyRange.NumberFormat = "\$#,##0.00"

[YasserKhalil]

Thanks a lot my tutor. so you mean the backslash used before the format does the trick?

[HansV]

Some characters in a number format string have a special meaning: for example m d h s $ # 0 . , - ( ) ;
A backslash before such a character tells Excel to treat it as a literal character.

[YasserKhalil]

Thanks a lot my tutor.
Best Regards