Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
For Each oneCell In Target
With oneCell
If .Column = 3 Then
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm"
Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 24, 25, 26, 27, 28, 29, 30
.NumberFormat = "dddd d""th"" mmm"
End Select
End If
End If
End With
Next oneCell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
Dim keyRange As Range
Set keyRange = Range("B7:F7")
On Error Resume Next
Set keyRange = Application.Union(keyRange, keyRange.Precedents)
On Error GoTo 0
If Not Application.Intersect(Target, keyRange) Is Nothing Then
For Each oneCell In Application.Intersect(Target, keyRange)
With oneCell
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm"
Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 24, 25, 26, 27, 28, 29, 30
.NumberFormat = "dddd d""th"" mmm"
End Select
End If
End With
Next oneCell
End If
End Sub