How to format a number

Originally Posted by shg

Code goes in the appropriate sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target(1)
        If .Column = 1 And VarType(.Value) = vbString Then
            On Error GoTo Oops
            Application.EnableEvents = False
            .Value = Format(Replace(.Value, " ", ""), """00""@@ @@@ @@@ @@ @@@@ @@@@ @@@")
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

Thanks but how does it know which cell to format?