Highlight a cell based on another cell compared to a range

[mst3kr]

Hello all!

Need some direction on whether VBA or Conditional Formatting is the best/correct avenue for what I'm after...

I have a spreadsheet that will be tracking all of the start-to-finish checkpoints in different phases for tasks that need to be completed for a customer, their new product, and when that product is due.

I need to highlight the customer name
Red if, based on the due date, any of the check dates for that customer will be 2 days or less than the due date.
Yellow if any of the check dates are within 4 days of the due date.
Green if all of the checkpoint dates are greater than the production date by at least 2 days.

Below is an example of what I'm after...

Customer

Due

Initial Data Rcvd​

Check 1

Check 2

Check 3

Check 1

Check 2

Check 3

Check 1

Check 2

Check 3

Check 4

Check 5

Check 1

Check 2

Check 3

Check 4

Magnificent Mike's

3/1/22

2/1/22​

2/1/22

2/2/22

2/3/22

2/10/22

2/12/22

2/15/22

2/16/22

2/16/22

2/16/22

2/20/22

2/21/22

2/23/22

2/25/22

2/25/22

2/25/22

Brainy Brian's

4/15/22

3/1/22

3/2/22

3/2/22

3/8/22

3/9/22

3/10/22

3/10/22

3/15/22

3/16/22

3/17/22

3/22/22

4/2/22

4/3/22

4/6/22

4/6/22

4/10/22

Jazzy Joe's

2/20/22

2/10/22​

2/11/22

2/11/22

2/11/22

2/12/22

2/13/22

2/15/22

2/18/22

2/20/22

2/23/22

2/26/22

2/26/22

2/28/22

2/28/22

3/2/22

3/20/22​

Magnificent Mike's would be highlighted Yellow since the final check date is 2/25 and within 4 days of the due date. Brainy Brian's would be highlighted Green, and Jazzy Joe's would be Red as we are way outside the due date. I'm already conditional formatting the individual checkpoints R/Y/G based on the due date. I just can't get that to translate to the customer cell.

Again, wasn't sure if this would be best suited for VBA or Conditional Formatting. I'm guessing CF but I haven't been able to make it work...

Thanks in advance for your help!
-Greg

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Customer	Due	Initial Data Rcvd	Check 1	Check 2	Check 3	Check 1	Check 2	Check 3	Check 1	Check 2	Check 3	Check 4	Check 5	Check 1	Check 2	Check 3	Check 4
2	Magnificent Mike's	01/03/2022	01/02/2022	01/02/2022	02/02/2022	03/02/2022	10/02/2022	12/02/2022	15/02/2022	16/02/2022	16/02/2022	16/02/2022	20/02/2022	21/02/2022	23/02/2022	25/02/2022	25/02/2022	25/02/2022
3	Brainy Brian's	15/04/2022	01/03/2022	02/03/2022	02/03/2022	08/03/2022	09/03/2022	10/03/2022	10/03/2022	15/03/2022	16/03/2022	17/03/2022	22/03/2022	02/04/2022	03/04/2022	06/04/2022	06/04/2022	10/04/2022
4	Jazzy Joe's	20/02/2022	10/02/2022	11/02/2022	11/02/2022	11/02/2022	12/02/2022	13/02/2022	15/02/2022	18/02/2022	20/02/2022	23/02/2022	26/02/2022	26/02/2022	28/02/2022	28/02/2022	02/03/2022	20/03/2022
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4	Expression	=MAX($D2:$R2)>=$B2+2	text	YES
A2:A4	Expression	=MIN($D2:$R2)>=$C2+1	text	NO
A2:A4	Expression	=MAX($D2:$R2)<=$B2+4	text	YES

 

[mst3kr]

Thank you Fluff! This worked perfectly! I never even considered the 'Stop if True' boxes making a difference with the formatting. Now, I know!

 

[Fluff]

You're welcome & thanks for the feedback.