System Timer

[psyopr]

1. I need to change a cell's data, and have another cell indicate the time when it was changed. The problem I have is when I use =IF(D2>0,NOW()) it updates all of my cells to that time. I need to track inidvidual cell time entries. Would like to 24 hour time ie 1345

2. Next problem is how to do a system comparison of how long it has been since the cell was changed like a conditional format to change color of the above cell. I have no idea how to compare against the system timer or other counter chronomter to track how long it has been since a cell changed.


Thank you!

 

[Paul B]

For number 1, try this by Barrie Davidson

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Barrie Davidson
'will put date and time in column B when something is put in A
' date will change if new data is entered
'change now to time() or date() to show time or date
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

 

[psyopr]

Am using Office XP, and can't seem to get that to work. Also, before whenever I ran NOW() anywhere/anytime even in an unrelated cell, it updated ALL of my NOW() entries, almost like NOW() forces a recalculate.

Column D is my data entry column, and column E is my time indication column

In XP I tried

=IF D:D=1,THEN E:E OFSET(0, 1), NOW() END IF END SUB

Also

=IF(D:D=1,THEN E:E OFSET(0, 1), NOW()) END IF END SUB
This tells me there are too few arguments and highlights OFFSET (0, 1)

Any thoughts, also, is it OK that I have labels in row 1 of these columns?

Thank you!

 

[VoG]

To get the above code to run, right click the worksheet tab, View Code and paste it in.

To get a Now() to stick, enter =NOW() then F9 then Enter.

 

[psyopr]

OK, am making progress thanks to VoG. I entered the following via view code on tab, still not quite working though

If D.D = 1 Then
E.E Offset(0, 1).Value = Time()
End If
End Sub

I get an error in the D.D, is that the right way to refer to a target column?

 

[NateO]

Howdy psyopr, welcome to the board. You'll either want to refer to the range as

range("d:d") or [d:d]

I'll use the latter and try to streamline this a touch:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [d:d]) Is Nothing Then _
Target(, 2) = Time 'assuming you want the Time in [E:E] _
Change 2 to 3 if you want the time in [F:F]
End Sub</pre>

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-05 20:04

 

[psyopr]

Thank you so much. Stupid me just realized after much help file research that all of the original BBS posting string needed to be in there, i.e. ByVal etc. OK, got that working well. Now to my last issue

I need to indicate the time difference between that cells change time and the system clock or most preferred, have it turn colors based on some threshold depending on how long ago the cel changed. EXAMPLE Below

cel D4 was changed at 20:35 as shown in E4, need to have E4 change to yellow if that was more than 5 minutes ago from the system clock.

Thanks again!

 

[psyopr]

- I know I messed up, just learned that in the help file, understand syntax a bit more now
On 2002-11-05 19:15, psyopr wrote:
OK, am making progress thanks to VoG. I entered the following via view code on tab, still not quite working though

If D.D = 1 Then
E.E Offset(0, 1).Value = Time()
End If
End Sub

I get an error in the D.D, is that the right way to refer to a target column?