Hi, I use Excel to track date when cell A1 have changed.

In followed codes, B1 are supposed to automatically record the date when I
changed content of A1. I use formula Month(B1) in C1 and Day(B2) in D1. It
works when I started in January, so when I made any change in A1 cell, B1
cell will automatically shows the Date when change happened(format of date is
'dd/mm/yyyy'), C1 cell will show month of date, D1 will show day of date.

----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A1")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, 1).Value = Format(Now, "dd/mm/yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
----------------------------------

it works.

THE strange thing happened in Feburay, when I made any change in cell A1,
the B1 show date in the format of ''mm/dd/yyyy", C1 show day instead of
month, D1 shows month instead of day.

Is there any suggestion? Thanks in advance.

Try this variation

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A1")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd/mm/yyyy"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jiang" wrote in message
...
Hi, I use Excel to track date when cell A1 have changed.

In followed codes, B1 are supposed to automatically record the date when I
changed content of A1. I use formula Month(B1) in C1 and Day(B2) in D1.
It
works when I started in January, so when I made any change in A1 cell, B1
cell will automatically shows the Date when change happened(format of date
is
'dd/mm/yyyy'), C1 cell will show month of date, D1 will show day of date.

----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A1")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, 1).Value = Format(Now, "dd/mm/yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
----------------------------------

it works.

THE strange thing happened in Feburay, when I made any change in cell A1,
the B1 show date in the format of ''mm/dd/yyyy", C1 show day instead of
month, D1 shows month instead of day.

Is there any suggestion? Thanks in advance.