Sub FiveCharacters()
Dim cel As Range, rg As Range
Set rg = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1))
If rg.Row = 1 Then Set rg = Range(rg.Cells(2), rg.Cells(rg.Cells.Count))
rg.NumberFormat = "@"
For Each cel In rg.Cells
If IsNumeric(cel.Value) Then cel.Value = Format(cel.Value, "0000#")
If Len(cel.Value) <> 5 Then
cel.Value = InputBox("Account number must be 5 characters. " & cel.Value & " is not. What should it be?")
End If
Next
End Sub
Sub FiveCharacters()
Dim cel As Range, rg As Range
Set rg = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1))
If rg.Row = 1 Then Set rg = Range(rg.Cells(2), rg.Cells(rg.Cells.Count))
rg.NumberFormat = "@"
For Each cel In rg.Cells
If IsNumeric(cel.Value) Then cel.Value = Format(cel.Value, "0000#")
Next
End Sub