Timestamp data entry and highlight when no data entered after 10 minutes

Hello,
I am trying to make a separate time stamp for data entered in cells (E1:E35) preferably have the individual time stamps next to the data cells ie. (D1:D35). I also need the time stamps font to turn red after ten minutes of no data entry in the coresponding cell. Thank you in advance.

Hi jdcarter and welcome to ExcelForum,

See the attached file which works as follows:
a. An Endless loop that (that repeats every 10 seconds) colors expired cells red in Column 'D', starts automatically when the workbook that opens.
b. The cell expiration time is 10 minutes.
c. Each time a cell in 'E1' thru 'E35' changes value, the cell in the same row in Column 'D' is Timestamped, and the Font in the cell is set to the Default font color.
d. To stop the Endless loop for any reason, Left Click the Yellow shape that STOPS the Endless loop.
e. To RESTART the Endless loop (actually stops and then starts the Endless Loop), Left Click the Yellow shape the RESTARTS the Endless Loop.
f. When the Workbook closes, the Endless Loop automatically stops.

In the ThisWorkbook code module:

Please Login or Register  to view this content.

In the 'Sheet1' code module:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

You are amazing! Worked perfectly. I know I'm being picky at this point but I didn't think I would get a solution. I've tried all day using the descriptions provided within the code you provided but still am lacking the expertise. The last thing I need to accomplish is make the time stamp only display time in 24 hr format hh:mm and change to another color once an additional five minutes has elapsed since last data entry. Ultimately going from normal to yellow after ten minutes then red after 15 minutes of no data entry. Is this even possible? Again thank you in advance.

Hi,

The last thing I need to accomplish is make the time stamp only display time in 24 hr format hh:mm and change to another color once an additional five minutes has elapsed since last data entry. Ultimately going from normal to yellow after ten minutes then red after 15 minutes of no data entry.

Since it is difficult to see yellow fonts, I changed the background color of the cell to Yellow after 10 minutes and the background color of the cell to red after 15 minutes.

To Format the Time in Column D as time in 24 hour format:
a. Right Click the 'D' above row 1 to select Column 'D'.
b. Format Cells > Number > Time > 13:30 > OK

The following link is a pretty good date/time formatting tutorial: https://www.ablebits.com/office-addi...-format-excel/

The following code is in the attached file. Major changes are in red:

In the ThisWorkbook code module:

Please Login or Register  to view this content.

In the Sheet1 Code Module:

Please Login or Register  to view this content.

Lewis