Results 1 to 4 of 4

Thread: Update date on change and conditionally format color

  1. #1
    Neophyte Jmker's Avatar
    Join Date
    Sep 2021
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Update date on change and conditionally format color



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files
    -Joe

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,039
    Articles
    0
    Excel Version
    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.
    Attached Files Attached Files

  3. #3
    Neophyte Jmker's Avatar
    Join Date
    Sep 2021
    Posts
    3
    Articles
    0
    Excel Version
    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.
    -Joe

  4. #4
    Neophyte Jmker's Avatar
    Join Date
    Sep 2021
    Posts
    3
    Articles
    0
    Excel Version
    2016
    I figured it out! Thanks anyway!
    -Joe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •