# Enter Timestamp for each time cell value changes

#### michaelyeehaw

##### New Member
I have dynamic low of day (LOD) stock prices in column A and last stock prices (LSP) in column B. The data is pulled from an external web service and never hard coded. I need to create 11 additional columns of data based on these two values:

Column C would display the times the LSP fell below LOD plus .01
Column D would display the times the LSP fell below LOD plus .02
Column E would display the times the LSP fell below LOD plus .03
Column F would display the times the LSP fell below LOD plus .04
and so on

For example: The LOD in A5 is 10.5.

C5 would display the timestamps for when the LSP fell below 10.51
D5 would display the timestamps for when the LSP fell below 10.52
E5 would display the timestamps for when the LSP fell below 10.53
F5 would display the timestamps for when the LSP fell below 10.54
...
M5 would display the timestamps for when the price fell below 10.61

If the LOD fell to 10.49, all the data would shift right one column, with C5 the only cell to display the new timestamp as the LSP fell below 10.50 and M5 now displays the timestamps for when the LSP fell below 10.60 as the data had been shifted right one column (previous data in M5 is now gone).

If the LOD suddenly changes from 10.50 to something like 10.45 at 10:30:29 AM, the data shifts right 5 rows such that rows I to M disappear to make way for the five fresh new sets of data in rows C to G, each containing the one timestamp 10:30:29 AM.

Upward movements of the LSP wont necessitate a timestamp. Only negative movements of the LSP below LOD plus .11 will necessitate a new timestamp, each separated by a semicolon (, in the rows represented by the prices the LSP decreased below.

Note: if the LOD is 10.50, a sudden move in the LSP from 10.55 to 10.53 would only one time stamp for Columns G and F as the LSP decreased below LOD plus .05 and LOD plus .04.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Yongle

##### Well-known Member
Welcome to MrExcel

Event macro Worksheet_Calculate can be used to do what you require

First a little test
Place code below in the sheet's code window - do not place it anywhere else as it will not work
Right click on sheet tab\ View Code \ paste code into that window
VBA Code:
``````Private Sub Worksheet_Calculate()
Debug.Print Now
End Sub``````

Explanation
The code simply writes something like this to the immediate window
25/07/2020 08:15:51

The immediate window is displayed in VBA editor with {CTRL} g or via the menu option View \ImmediateWindow
Select

Results
Have a look in the immediate window AFTER the values have been updated ONCE
Is the code triggered when the values in the sheet are updated ?
- if not, place this volatile formula in any unused cell in the worksheet
=NOW()
The code will be triggered when you enter the formula
I also expect it to be triggered when the values in the sheet are autoupdated

Q How many times is the time stamp written to the immediate window each time your values are auto-updated ?

#### michaelyeehaw

##### New Member
This worked well. i ran the code, made some changes, and then the changes were reflected in the window. Eager to know the next steps

#### Yongle

##### Well-known Member
I ask questions because I do not see what is going on in your computer
So it is important that you answer every question that is asked

What is the answer to the question in the last line of post#2 ?
thanks

#### michaelyeehaw

##### New Member

the answer is the window updated each time i changed the sheet. i delete the window contents and tried several times with various changes to verify this was the case

#### Yongle

##### Well-known Member
Multiple values are updated concurrently when the sheet is auto-updated.
When that happens is the code triggered ONCE or more than ONCE?

If you cannot answer the question ...
- delete all values in the sheet
- delete whatever is in the immediate window
- and have the sheet fully auto-updated
- look in immediate window to see how many times the date stamp appears

I need confirmation that the code is only triggered once
thanks

#### michaelyeehaw

##### New Member
cannot test with dynamic data at the moment

Ok, no problem
thanks

#### michaelyeehaw

##### New Member
ok i tried it out with dynamic data and it only updates with one timstamp when 1 change made and 3 when i made 3 changes

Replies
2
Views
519
Replies
2
Views
3K
Replies
2
Views
335
Replies
4
Views
497
Replies
0
Views
327

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.

1,163,951
Messages
5,834,535
Members
430,295
Latest member
amdis

### 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.

### Which adblocker are you using?

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

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