I need a formula or something in VBA that will help me do mass changes to the amount of decimal points allowed in a cell. I'd like to be able to set the amount of decimal points in one cell, and then it will change it across all of my sheets.
Assuming A1 is the cell on a Sheet where you apply a number format manually and then apply the same number format to other sheets in the workbook, place the following code on the Sheet Module.
To do so, right click the Sheet Tab --> View Code --> and paste the code given below into the opened code window.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet
If Target.Address(0, 0) = "A1" Then
Cancel = True
For Each ws In ThisWorkbook.Worksheets
'Nummber format will be applied to the columns B:G on each Sheet in the Workbook
'Change it as required
ws.Columns("B:G").NumberFormat = Target.NumberFormat
As per the above code, once you change the number format in A1 on the Main Sheet and double click in A1, the same number format will be applied to the columns B:G on all the sheets in the workbook.
To test the code, change the number format in A1 (yellow cell) and double click in A1 to run the code.