Excel doesn't have a click event that you can use. But it does have a
_beforedoubleclick and _beforerightclick.
This uses the beforerightclick event:
Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim myRng As Range
Set myRng = Me.Range("A:a") 'all of column A
Set Target = Target.Cells(1) 'one cell only
If Intersect(Target, myRng) Is Nothing Then
'do nothing
Else
Cancel = True 'don't show the rightclick popup menu
With Target
.NumberFormat = "mmmm dd, yyyy"
.Value = Date
End With
End If
End Sub
Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this code into the newly opened code window (usually on the right
hand side).
Then back to excel to test it out.