Make the number formatting consistent across multiple sheets and ranges of data

Question

Dear community,

I would like to use the same number format across different sheets and as I have a very large workbook with many sheets it would be a lot of effort to manually adjust every section in the workbook that contains numbers. Is there any way to select all text that is in a certain number format and to replace it with a new number format?

Many thanks!

Answer 1

You could run the following macro.

Before you do so, make sure that there is at least one cell in the workbook that has the old number format, and at least one other cell that already has the new number format. Otherwise, the code will throw an error.

Change the number formats in the code to the ones you want to use!

Sub ReplaceFormatting()
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    ' Change as needed.
    Application.FindFormat.NumberFormat = "#,##0.00"
    Application.ReplaceFormat.NumberFormat = "0.00"
    For Each wsh In Worksheets
        wsh.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchFormat:=True, ReplaceFormat:=True
    Next wsh
    Application.ScreenUpdating = True
End Sub