Update date on change and conditionally format color

Jmker

New member
Joined
Sep 8, 2021
Messages
3
Reaction score
0
Points
1
Excel Version(s)
2016
I am trying to make column I update with current date when the comment in column H changes
Make comment and date fill turn yellow when date is 7 days old
Make comment and date fill turn red when date is 14 days old

Spreadsheet is populated with old sample data.

Prefer to not use VBA but ok if not possible.
 

Attachments

  • Project Status Update Copy.xlsx
    24.1 KB · Views: 5

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,201
Reaction score
16
Points
38
Excel Version(s)
365
You won't be able to enter today's date automatically after changing a cell without vba.
There's vba in the attached. It's basic, so will update even if you delete the contents of a column H cell, and even if you go in to edit it, change nothing then press Enter (rather than Escape). Another snag with vba is that should you accidentally delete cells in column H, you won't be able to Ctrl+z (undo) to bring it back.

There are 2 conditional formats too, both applying to I2:I40, to change the colour of the cells. Currently an empty cell still shows red, but that's tweakable depending on how ingenious you want to be with the CF formula.
 

Attachments

  • ExelGuru11299 Project Status Update Copy.xlsm
    30.6 KB · Views: 3

Jmker

New member
Joined
Sep 8, 2021
Messages
3
Reaction score
0
Points
1
Excel Version(s)
2016
Thanks, it is working perfectly but I have another issue I can't upload it to a gov computer because it gets stripped out of email. I can have a macro enabled in my file system so I copied the code and conditional formatting in notepad and emailed it to myself and it is working on my gov computer but when I change a comment it removes the conditional fill color from the cell above it but inserts the date in the proper cell. The conditional formatting is off by one cell!
Do you have any idea how to fix this? I have been fiddling with it for an hour so far.
Thanks.
 

Jmker

New member
Joined
Sep 8, 2021
Messages
3
Reaction score
0
Points
1
Excel Version(s)
2016
I figured it out! Thanks anyway!
 
Top