Conditional formatting with auto entry

**jdcarter** · 09-14-2015, 06:31 PM

Hello. I'm trying to set up a cell that when data is entered it puts the time it was entered in the next cell over. I also need that time to turn red if no data has been entered in the original cell within ten minutes. Thank you in advance.

**gmr4evr1** · 09-14-2015, 07:20 PM

I can help you out with the 1st part. Place this in the sheet you want the time to be entered

Please Login or Register  to view this content.

Or, see attached
Cell Time Change.xlsm
Change cell A1 to see it work

**jdcarter** · 09-14-2015, 07:52 PM

Looks good so far. Thank you. Is it even possible to do the other thing I need it to do?

**gmr4evr1** · 09-14-2015, 07:54 PM

It should be with VBA, I just haven't figured out how to do it yet.

**jdcarter** · 09-14-2015, 08:08 PM

I thought there had to be a way, but I'm not very good with code.

**gmr4evr1** · 09-15-2015, 08:30 AM

I'll keep trying to get it figured out when I can, in the meantime, you might want to start another thread with for having "....time to turn red if no data has been entered in the original cell within ten minutes."

If you do that, include the link to this thread in your post so others can see what was originally happening. Also, if you create a new thread, mark this one as solved.

**6StringJazzer** · 09-15-2015, 09:24 AM

Originally Posted by jdcarter

I also need that time to turn red if no data has been entered in the original cell within ten minutes.

Ten minutes starting from when?

By the way, I suggest you update your title. This problem is not a conditional formatting problem. It's better to state what you want to do, rather than how you think it should be done. A better title would be "Timestamp data entry and highlight when no data entered after 10 minutes"

**jdcarter** · 09-15-2015, 04:29 PM

Thank you for the suggestion. I don't know how to update the title via iPhone. My home computer's drive failed and my work computer blocks this site. I need the time stamp to turn red after ten minutes of the time stamp being updated to remind the user to update the coresponding data cell. The data cells should be range (E1:E35). The time stamps should be (D1:D35). I need it to do this until the program is closed. I hope all of that makes sense.

**6StringJazzer** · 09-17-2015, 04:05 PM

I need to understand the overall scenario. Here is what I think is going on.

You have a list of data in one column, and a list of timestamps in the next column. If the user changes any piece of data, the timestamp is updated, and the user must update it again within 10 minutes. After 10 minutes have passed, the timestamp will turn red.

Unfortunately gmr4evr1's code updates the timestamp when you select the data, not when you update it. Here is a correction:

Please Login or Register  to view this content.

I have included that code in the attached.

I have also added a conditional formatting rule to turn timestamps to red font if they are older than 10 minutes. However, note that this only happens if there is some user action that forces a recalculation, like entering more data. It will not happen if the user just sits. If you need that, then it will be necessary to add timers into the code.

There are also some boundary conditions that you don't mention.

Is there any requirement to add new data within 10 minutes of first opening the file?
What if the file is open and there are times leftover from yesterday? Unless the timestamp includes the day, you won't know that is was yesterday. Is the day important?
How does data get entered when you're starting with a blank file?