Register
Log In
Unanswered
Home
Dashboards
April 21, 2015
Offline
| The Excel newsletter of 30th June explains how to apply formatting by macro. The formatting I applied was show negatives in brackets and red. |
| This works fine when recording the macro but when running the macro on a range although it shows negatives in red it precedes the value with the negative sign rather than place the values in brackets. |
See Answer
Dashboards
June 25, 2016
Offline
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
Dashboards
April 21, 2015
Offline
SunnyKow said
Hi HigginsTry this
Sub Red()
Selection.NumberFormat = "#,##0_);[Red](#,##0);"
End SubYou 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
Answers Post
Dashboards
June 25, 2016
Offline
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
October 5, 2010
Offline
Hi Jimmy,
this article explains some more about custom formats
https://www.myonlinetraininghub.com/excel-custom-cell-formats
Regards
Phil
Dashboards
April 21, 2015
Offline
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
Most Users Ever Online: 57
Currently Online: Ali Bokhari
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ali raza
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
