AutoFormat an Excel cell

This Excel 2003 macro formats a cell depending on its contents when you type something in it. If you type a number, or a formula that returns a number, it is formatted one way, if you type a date it is formatted another way and if you type a word it is formatted a different way.

The macro uses the OnEntry event which fires whenever something is entered into a cell. Attach the macro to an Auto_Open macro to ensure it is run whenever the workbook is opened.

To create this, choose Tools, Macro, Visual Basic Editor and, choose Insert, Module to add a module to the current worksheet then type the code into it.

Back in Excel choose Tools, Macro, Auto_open to run the macro the first time to test it. Provided you have Excel configured to run macros, it will run automatically every time you open the workbook in future.

Sub Auto_Open()
ActiveSheet.OnEntry = "formatCell"
End Sub

Sub formatCell()
If IsNumeric(ActiveCell) Then
ActiveCell.Font.Name = "Verdana"
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 46
ElseIf IsDate(ActiveCell) Then
ActiveCell.Font.Name = "Verdana"
ActiveCell.Font.Size = 10
ActiveCell.Font.ColorIndex = 50
Else
ActiveCell.Font.Name = "Times New Roman"
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 5
End If
End Sub

Sub Auto_Close()
ActiveSheet.OnEntry = ""
End Sub