Code Bug

  • Thread starter Thread starter Jack Foster
  • Start date Start date
J

Jack Foster

Hi...

I'm trying to time stamp a key stroke...when a user keys a
Y I want a cell 3 to the left top enter now(). I think the
code is perfect but it just won't work...Can anyone do
this better...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("AD8:$AD700")) Is Nothing
Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
End If
End If
End If

Cheers

GC
 
Jack,

What is going wrong? Is it going 3 right instead of 3 up? If so, change
Offset(0, 3) to Offset(-3,0). If not, enlighten us.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
It seems to work for me. One thing you may want to do when you'r
changing a cells value using the Worksheet_Change event is to tur
events off before changing the cell and turn it back on afterwards:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A8:$A700")) Is Nothing Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Application.EnableEvents = False
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
Application.EnableEvents = True
End If
End If
End If
End Su
 
Back
Top