Stuart uses the NumberFormat property, in a macro, to set the custom format for a cell. He sets it equal to #,##0.00_);[Red](#,##0.00), but when he later looks at it, Excel changes the format to #,##0.00;[Red]-#,##0.00.
You can set the custom format of a selection of cells in this manner:
Sub SetCells()
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
End Sub
Similarly, you could set the custom format of a specific range of cells in this manner:
Sub SetCells()
Range("A1").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
End Sub
Either approach sets the custom format correctly, as confirmed through testing. If the format doesn't appear as expected (and you are sure the format pattern matches what is shown above), then there are only two reasons I can think of that would cause the discrepancy. First, it is possible that other macro code is changing the custom format without your knowledge, particularly code in an event handler. Why an event handler? Because event handlers often make changes automatically, typically after cell changes or even moving from one cell to another.
The second possibility is that what you are seeing in the worksheet is not the result of the custom format, but actually the result of a conditional format. You'll need to check and possibly change the conditional formatting rules to deal with this possibility.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12941) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Adding a custom format to Excel is easy. Having that custom format appear in all your workbooks is a different story ...
Discover MoreExcel allows you to format your numeric values in a wide variety of ways. One such formatting option is to display ...
Discover MoreCustom formats are great for defining how a specific value in a cell should look. They aren't that great at doing complex ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-10-03 18:28:06
kiwerry
@ J. Woolley Thank you for adding another test - if I remember correctly, you are also in the US and would use the same regional settings as Allen, so it works for you as well. My regional settings (and, presumably, Stuart's) being different was the only reason I found to explain why the macro didn't work for me.
My test of the macro was done by inspection in the formatting dialogue, independent of the macro's message; that simply informed me that it had tried to set the string shown as the format in the given cell, whereafter I went to see the format for myself.
BTW The "Private" still being there is simply to prevent sandbox macros from cluttering up the Macro List in Excel; I should probably have deleted it after pasting in my comment.
2026-10-02 10:59:30
J. Woolley
Using Win11 and M365, I modified Kiwerry's macro slightly. (I don't know why Kiwerry's macro was declared Private; mine is Public by default.)
Sub SetCellFormat2()
With Selection
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
MsgBox .Address & " NumberFormat:" & vbLf & .NumberFormat
End With
End Sub
My results matched Allen's; the macro "sets the custom format correctly, as confirmed through testing."
2026-10-01 05:01:22
Kiwerry
P.S. A possible workaround could be to define the custom format once manually, then use a macros to copy a cell with that format (it works if defined manually) and paste the formatting where it is needed.
2026-10-01 04:54:56
Kiwerry
Interesting. I copied Allen's macro and edited it slightly, see below. The message box is simply there to confirm that it was looking at the cell I expected it to.
I tried it out in a new blank workbook in two cells, one with a positive number, the other negative.
In each case the resulting custom format used by Excel was "#,##0.00;[Red]-#,##0.00"; as happened in Stuart's case. This behaviour persisted, even after I defined and checked Stuart's custom format in a third cell manually (it worked as expected, bracketing the negative number). I had thought that once Stuart's format was in the custom list it would use it, but it kept to its version .
No conditional formats existed in the blank workbook, but the system regional number format setting for negative numbers on my machine is "-1", rather than "(1)", whereas on Allen's machine as used for the check, brackets are used for negative numbers. I suspect that this may be the cause of the problem.
Environment: Win 10, Office 365
Private Sub SetCellFormat()
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
MsgBox Selection.row & ", " & Selection.column & vbTab & "#,##0.00_);[Red](#,##0.00)"
End Sub
2026-10-01 04:52:49
jamies
Maybe merged cells ?
And try recording a macro of you doing that through the excel UI
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Comments