Use of NumberFormat function chnages cell type from "Number" to "Custom"

[askou]

Hi all,

I am trying to build a simple number formatting macro that would display negative numbers in parentheses e.g., -24000 should be shown as (24,000.00) or something like that. While I am able to accomplish said formatting change using Numberformat, the command is forcing the cell type to change from Number to Custom. Is there any chance that I can prevent this from happening?

Below the code I am using:

Sub FormatNum_Coma_2Decim(control As IRibbonControl) Dim SelRange As Range
Set SelRange = Selection
 
SelRange.NumberFormat = "#,##0.00;(#,##0.00)"
End Sub

Any help appreciated,
Best,
Andreas

 

[Jonmo1]

Because it actually IS a custom format, not one of the standard built in formats.

Try
SelRange.NumberFormat = "0.00_);(0.00)"

This is like going to format - cells - Number and choosing the black parens in the box for negative values.