auto fill in

spm2002

New Member
Joined
Sep 24, 2002
Messages
2
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,457
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

New Member
Joined
Sep 24, 2002
Messages
2
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,457
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

bart.gif

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,088
Messages
5,835,314
Members
430,351
Latest member
ddalton

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top