MS Excel Conditional Formatting Formula to apply on a date cell value only for six days when date changes. but applying itself same conditional formatting onto the new date given to highlight more potential future changes.

Question

MS Excel Conditional Formatting Formula to apply on a date cell value only for six days when date changes. but applying itself same conditional formatting onto the new date given to highlight more potential future changes.

David Tobon 0

Hi all,

I'm creating an Excel sheet to track the timeline of 200+ projects and their constant changes in dates.

I've created a conditional formatting to highlight this changes in red color and bold font to work on this for a week until a new bunch of changes comes up, but I need those highlighted changes to return to the original spreadsheet format automatically a day before we enter the new changes for the week ahead.

This is where I'm struggling at the moment, finding a formula that changes those dates in red and bold font, back to the spreadsheet format, but, that at the same time, apply the conditional formatting based on the new date for potential future changes.

I hope I made myself clear with my communication on what I'm looking to achieve despite my poor technical vocabulary. Please see below a small example of the exercise:

1.Original dates of the projects

User's image

First set of changes by conditional formatting. They should stay highlighted in red for the next six days. By then, ideally they should return to original format, so the spreadsheet is ready for the new set of changes.

User's image

Following set of changes. As we can see the date in cell B3 returns to original formatting, where cell C4 has applied the conditional formatting for the second time (done manually)

User's image

Thank you very much in advance for any help or advice you could share.

Barry Schwarz 3,746 Reputation points

2023-09-26T07:19:39.31+00:00
I don't think conditional formatting can do what you want. It doesn't know how long ago a particular format was applied or how long ago a cell was modified.

You can use two macros:

The first macro should be run just before you start to update entries. It should find each cell that has the "highlighted" format and change it to the "normal" format.

The second should be executed manually as you change the contents of a cell. It should simply apply the desired format to this cell.
David Tobon 0 Reputation points

2023-09-28T17:03:57.7166667+00:00

Barry,

It's a shame though. I thought that would be the case.

Thank you very much for your answer and suggestion.

Your answer

Barry Schwarz 3,746 Reputation points

2023-09-26T07:19:39.31+00:00

I don't think conditional formatting can do what you want. It doesn't know how long ago a particular format was applied or how long ago a cell was modified.

You can use two macros:

The first macro should be run just before you start to update entries. It should find each cell that has the "highlighted" format and change it to the "normal" format.

The second should be executed manually as you change the contents of a cell. It should simply apply the desired format to this cell.
David Tobon 0 Reputation points

2023-09-28T17:03:57.7166667+00:00

Barry,

It's a shame though. I thought that would be the case.

Thank you very much for your answer and suggestion.

Share via

MS Excel Conditional Formatting Formula to apply on a date cell value only for six days when date changes. but applying itself same conditional formatting onto the new date given to highlight more potential future changes.

Your answer