cell with static current time

B

ben w

i would like a cells in a column to display the curent static time the cell
in the previous column is a certain value
ex:
if cell value is "out" or not "in" then next cell to display when value
changed.
any answers? macros, formulas, setting changes?
 
G

Gary''s Student

Does the cell change because of manual entry or does it have a formula in it?
 
B

ben w

cell with list causes insertion of current static time in another cell, when
original cell is a certain value
 
G

Gary''s Student

Here is an example using A1 and B1. Data goes in A1 and the time is tagged
in B1. If you insert OUT (data validation or manually) the time is
statically recorded in B1. Once the time is entered, it will not update,
even if somebody comes along and messes with A1.

The only way to refresh B1 would be to clear it. That allows re-tagging:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Range("A1").Value <> "OUT" Then Exit Sub
If Range("B1").Value <> "" Then Exit Sub

Application.EnableEvents = False
Range("B1").Value = Time
Application.EnableEvents = True
End Sub

Note that this is Worksheet Event code. It does NOT go in a standard module.
 
B

ben w

thanks, this works, but i tried changing the range from just a1 to entire
column and out put to entire column b, but did not work, not that familiar
with this, how would i change it?
 
G

Gary''s Student

Hii ben:

Here is the new version that tags all of column A:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Target.Value <> "OUT" Then Exit Sub
If Target.Offset(0, 1).Value <> "" Then Exit Sub

Application.EnableEvents = False
Target.Offset(0, 1).Value = Time
Application.EnableEvents = True
End Sub


Study it to see how we went from a single cell to a column.


REMEMBER: You have to erase the old version.
 
Top