Dynamically change number formatting within a column

[mikerickson]

This uses a change event to adjust the cell formatting to match its contents.
Put in the code module for the desired sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim oneCell As Range
If Not (Application.Intersect(Target, Target.Parent.Range("Z:Z")) Is Nothing) Then
    For Each oneCell In Application.Intersect(Target, Range("Z:Z"))
        With oneCell
            .NumberFormat = IIf(IsDate(.Value) Or (9 < Val(CStr(.Value))), "m/d/yyyy", "0")
        End With
    Next oneCell
End If
End Sub