stop conditional formatting when date has passed even if condition is met


New Member
Apr 8, 2022
Office Version
  1. 365
  1. Windows
Dear all,

I am seeking assistance with locking the formatting of cells once one of the conditional formatting rules is met and today’s date has passed so the formatting is not “overwritten” once another conditional formatting rule for that same cell is met.

I have a table with each row representing an individual task (Column A). For each row I am able to select the status of the task (column B), e.g. “in progress”, “with Person A”, with Person B”, “complete”. Columns C to (x) are the days it takes to complete the task; i.e. Column C = Day 1 of the task, with the actual cell containing the date.

What I am trying to achieve is a colour-coded timeline, so I can see what the status of a given task is/was on a particular day throughout the task’s life (i.e. from start date of the task until it is complete). I would like to achieve this by assigning a colour to each possible status option and every time I change the status of the task, the colour of the cell with today’s date changes. With each new day the cell will auto fill with that same colour until I come back and change the status again, which is when auto-filling will continue with a different colour. Below is an example of what I would like the table to look like.


I have worked out the below formula, which says “change colour to (whatever colour I assign) when the status is “in Progress” and the date in the cell is older or matches today’s date.

=AND($B3="In Progress", L3<=TODAY())

Each cell has 4 rules – one for each status option. B3 and L3, of course, are adjusted for each cell as needed.

My problem with the above formula/rule is, as soon as I change the status the colour of all passed dates changes to the colour of the currently selected status. I kind of need another rule or condition that says “retain the colour of the cell if the date is older than today’s date, even if the status changes.”

I should add that the status options do not come in a particular order, i.e. the status of the task can change from “in progress” to “with person A” and back to “in progress”.

I am not sure if I may be needing a VBA code for this?

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
There is no way to differentiate that Cell B3 was "In Progress" on Day 2 but "Complete" on Day 10 so this approach (=AND($B3="In Progress", L3<=TODAY())) is never going to work for a timeline. If you want to use conditional formatting to display a color for day-by-day status, you need a source of status information that is tied to a date.
1TaskStatusCompletion DateDay1Day2Day3Day4Day5Day6Day7Day8Day9Day10
32In Progress30-Mar31-Mar1-Apr2-Apr3-Apr4-Apr5-Apr6-Apr7-Apr8-Apr
43In Progress30-Mar31-Mar1-Apr2-Apr3-Apr4-Apr5-Apr6-Apr7-Apr8-Apr
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:M5Expression=AND(TRIM($C2)<> "",D2<$C2)textNO
D2:M5Expression=AND(TRIM($C2)<> "",D2>=$C2)textNO
D2:M5Expression=$B2 = "In Progress"textNO
Upvote 0
Hi rlv01, thank you so much for your reply, it is very much appreciated. I am a bit short for time today to wrap my head around it. I will try your solution, hopefully tomorrow, and let you know how I go :)
Upvote 0
Hi rlv01, i just wanted to say thank you for helping me with this. Your formatting does what i need it to do :) Thanks again!
Upvote 0

Forum statistics

Latest member
tony tessman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back