Hi Higgins

Try this

Sub Red()
    Selection.NumberFormat = "#,##0_);[Red](#,##0);"
End Sub

You are just short of a semicolon at the end.

Sunny Kow

SunnyKow said
Hi Higgins

Try this

Sub Red()
    Selection.NumberFormat = "#,##0_);[Red](#,##0);"
End Sub

You are just short of a semicolon at the end.

Sunny Kow  

Hi Sunny

Your answer works fine, thanks.  Do you know why it needed the ; ?  When recording the macro it did not place a ; at the end but it did show the answers in brackets.  It was only when running the macro that brackets were not applied.  Is the ; a macro requirement and is it therefore likely to be needed to be added to other macro's?

Best regards

Jimmy
 

Hi Jimmy

Glad to know I was able to help.

I have no idea why the macro recorder failed to record the semi colon and why VBA needed it to format correctly. 

I rarely ever use the recorder as the codes generated are too long not too reliable.

Custom formatting consist of 4 parts, each separated by a semi colon. You have to let Excel know what is the formatting you want to apply to each part.

<Positive Values> ; <Negative Values> ; <Zero Values> ; <Text Values>

Frankly I just pushed my luck by adding the semi colon to your code and it works 🙂

Sunny Kow

Hi Jimmy,

this article explains some more about custom formats

https://www.myonlinetraininghub.com/excel-custom-cell-formats

Regards

Phil

Philip Treacy said
Hi Jimmy,

this article explains some more about custom formats

https://www.myonlinetraininghub.com/excel-custom-cell-formats

Regards

Phil  

Hi Phil

Thanks for the link.  I use custom formatting quite extensively and have attached a file with my favourite formats and formulas in case they are of interest to anyone.

Whenever I have used custom formatting, as Sunny has said above, the ; is used to separate the formatting but I have never seen a ; at the end before.  Sunny was clever enough to try that and it works.  Is this only a requirement within macros, presumably to tell the macro that this is the end of the formatting?

If so, are there any similar areas in writing macros that are good to know about so that the macro will work?

Thanks