auto fill in

[spm2002]

I am wondering if it is possible in Excel to have a cells value update when an adjacent cell is modified. Specifically
If cell C2 changes I would like D2 to show the time of the change.
I currently have a sheet with the C column using validation properties to limit entries to a list when a cell in the C column is updated or changed I would like the D column to show the time the update was made
I hope this makes sense.
This message was edited by spm2002 on 2002-09-25 20:02

 

[Colo]

Welcome spm2002,

If youe excel version is 2000 and later, it works.

<pre>
'Put this code your sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column <> 3 Then Exit Sub
For Each rng In Target
rng.Offset(, 1).Value = Now
Next
End Sub
</pre>

 

[spm2002]

thanks,
that helps, I have excel 2000 but this formatted sheet will be used by many others and I am not sure what versions they have
any thoughts on a solution that will be compatable with 97 and 2k?

again thank you
sm

 

[Colo]

Okay sm, Worksheet_Change event of XL97 does not works when validation is used.
If XL97 will be used, it will be solved for the setting "Validation" each time, like this. <pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column<> 3 Then Exit Sub
Dim strSeq As String
Dim rng As Range
For Each rng In [A1:A10]
strSeq = strSeq & "," & rng.Value
Next
For Each rng In Target
With rng
.Validation.Delete
.Validation.Add xlValidateList, Formula1:=strSeq
End With
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column<> 3 Then Exit Sub
For Each rng In Target
rng.Offset(, 1).Value = Now
Next
End Sub</pre>

_________________
HTH

With Regards,
Colo

This message was edited by Colo on 2002-09-25 22:57