This is quite crude, but seems to get the job done...
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' This macro, placed in a WorkSheet module will automatically
' copy a cell's value over to it's comment box, formatted as Month Day (th),
Year
' like June 1st, 2006, June 2nd, 2006, etc etc once the cell has been
activated.
On Error Resume Next
If Day(ActiveCell.Value) = 1 Or Day(ActiveCell.Value) = 21 Or
Day(ActiveCell.Value) = 31 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "st, "
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 2 Or Day(ActiveCell.Value) = 22 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "nd, "
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 3 Or Day(ActiveCell.Value) = 23 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "rd, "
& Format(ActiveCell.Value, "yyyy")
Else
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "th, "
& Format(ActiveCell.Value, "yyyy")
End If
End If
End If
End Sub
hth
Vaya con Dios,
Chuck, CABGx3