Number Format Macro Not Working Properly

[wsmurray]

I like a number format for negative numbers that is red has brackets around it. I created a simple macro so I can easily do this on every spreadsheet but I can’t get it to work properly. Macro is below.

Sub MyNumberFormat()
'
' MyNumberFormat Macro
'
' Keyboard Shortcut: Ctrl+n
'
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
End Sub

When you run it, there are no brackets. The format that it creates is #,##0.00;[Red]-#,##0.00 so it has dropped the brackets.

If you amend the macro to Selection.NumberFormat = "#,##0.00_);[Red](###0.00)" this works fine, it creates the brackets but of course I’ve dropped the comma so I still need to go into the format and edit it so I have a comma separating the thousands.

Any idea why the macro won’t set the format correctly?

Many thanks

Stuart

[rorya]

Try adding in the format for 0:

Please Login or Register  to view this content.

[wsmurray]

Brilliant. Thank you.