Number formatting

[gberg]

Is there a way to change all cells in a workbook that share a common number format?

I have a workbook with multiple tabs. I would like to be able to have a vba code to change the number formatting for all cells that have this #,##0_ );(#,##0) formatting to #,##0.0,_);(#,##0.0,) and then back again.

There are hundreds if not thousands of cells in my workbook that have the same formatting

 

[Ron Coderre]

I had a similar situation a few years ago.
Here's what I did:
• Created a Custom Cell Style that only featured a number format.
• Assigned that cell style to all impacted cells
• Used VBA to toggle the number format for the Custom Cell Style depending on a cell value in a "Control Panel" tab in my workbook

That way, one simple change to the Custom Cell Style can have far reaching impact on the workbook.

Is that something you can work with?

 

[gberg]

That's intriguing. So you would have one cell that would control the number formatting of all the similar type cells? How would the VBA work?

Thanks,
Greg

 

[gberg]

I found a solution

Worksheets("Sheet1").Range("F8").NumberFormat = "#,##0.0,_);(#,##0.0,)"
Application.FindFormat.NumberFormat = "#,##0_ );(#,##0)"
Application.ReplaceFormat.NumberFormat = "#,##0.0,_);(#,##0.0,)"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True