Selected Answer
Install the procedure given below in the code sheet of the worksheet on which you wish to control the number format. It will run every time you activate that sheet.
Private Sub Worksheet_Activate()
Const SampleRow As Long = 2
Dim Rng As Range
Dim C As Long
For C = 1 To UsedRange.Columns.Count
Set Rng = Range(Cells(SampleRow + 1, C), _
Cells(Cells(Rows.Count, C).End(xlUp).Row, C))
If Rng.Row > SampleRow Then
Rng.NumberFormat = Cells(SampleRow, C).NumberFormat
End If
Next C
End Sub
Look for the constant "SampleRow" in the above code. If necessary change its value to the number of the row in which you have the correct format. The code will loop through all columns and apply the Numberformat found in the SampleRow to all cells in the column. It could be extended to set other formats. For example, replace the line setting the NumberFormat with the following 3 lines to copy all formats, including colors, fonts and borders.
Cells(SampleRow, C).Copy
Rng.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
If your workbook only has one sheet (or if you prefer that kind of action) replace the first line - the declaration line - of the above procedure with
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
and place the procedure in the "ThisWorkbook" module. It will run when you save the workbook, meaning you will save the correct format.
You can force the code to run by switching sheets forth and back or, if attached to the Save event, by saving the workbook.
Despite of all the above I do agree with @queue that it is better to prevent wrong formatting than to repair it. Repair should only be necessary if you need to paste data and can't control the format of the data you paste. In all other cases I would prefer to search for a solution which ensures that cells are formatted corractly at the time of entering the data.
