System Timer

psyopr

New Member
Joined
Nov 4, 2002
Messages
6
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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
575
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

New Member
Joined
Nov 4, 2002
Messages
6
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

New Member
Joined
Nov 4, 2002
Messages
6

ADVERTISEMENT

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

Legend
Joined
Feb 17, 2002
Messages
9,700
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

New Member
Joined
Nov 4, 2002
Messages
6
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

New Member
Joined
Nov 4, 2002
Messages
6
- 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?
 

Forum statistics

Threads
1,144,766
Messages
5,726,173
Members
422,659
Latest member
RGP268

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