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

[excelnewby22]

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?

 

[rlv01]

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.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Task	Status	Completion Date	Day1	Day2	Day3	Day4	Day5	Day6	Day7	Day8	Day9	Day10
2	1	Completed	4/6/2022	30-Mar	31-Mar	1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr	8-Apr
3	2	In Progress		30-Mar	31-Mar	1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr	8-Apr
4	3	In Progress		30-Mar	31-Mar	1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr	8-Apr
5	4	Completed	4/4/2022	30-Mar	31-Mar	1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr	8-Apr
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:M5	Expression	=AND(TRIM($C2)<> "",D2<$C2)	text	NO
D2:M5	Expression	=AND(TRIM($C2)<> "",D2>=$C2)	text	NO
D2:M5	Expression	=$B2 = "In Progress"	text	NO

 

[excelnewby22]

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

 

[excelnewby22]

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!