Hi...

My user must enter yes occasionally in a descending range
A1:A500. When yes has been entered cells B1:B500 must
return the date that the YES was entered in the
corresponding horizontal cell. This is similar to my
previous query but now dealing in ranges rather than
cells...nb the date must not change its value once the
date has been triggered by the 'yes'.

Many thanks to the clever guys...

GC

Right click on the sheet tab and select view code. Paste in one of the
below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:A500")) Is Nothing Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "YES" Then
Target.Offset(0, 1).Value = Now()
Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm:ss"
Target.EntireColumn.AutoFit
End If
End If
End If
End Sub

for the entire column

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "YES" Then
Target.Offset(0, 1).Value = Now()
Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm:ss"
Target.EntireColumn.AutoFit
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

Gordon Cartwright wrote in message
...
Hi...

My user must enter yes occasionally in a descending range
A1:A500. When yes has been entered cells B1:B500 must
return the date that the YES was entered in the
corresponding horizontal cell. This is similar to my
previous query but now dealing in ranges rather than
cells...nb the date must not change its value once the
date has been triggered by the 'yes'.

Many thanks to the clever guys...

GC