Conditional Formatting

[Jennifer Van]

I already have the formula =IF(OR(ISBLANK(AC5)),"",AD5+181) in columns AD,AF,AH,AJ,AL giving me "review dates" six months from delivery (AC) and then 6 months increasing.

I have conditional formatting set up to highlight cells if overdue

In columns AE, AG, AI, AK,AM I have Yes - which mean I have completed the check.

What I want to happen now is the the previously red cell showing it was overdue, to now read the cell next to it saying yes and turn green showing it has been done? Is this possible


Also, just to make things harder, I want the formula to miss blank cells - once product is returned, I remove the dates to clear to sheet a bit but when I do, the previously red boxes remain - I have tried both ways of leaving formatting blank and selecting "blanks" but this does not work.

I already have the line using conditional format - so not sure how many conditions I can use?
Can someone help?

Thanks

 

[StephenCrump]

It would help if we could see your worksheet. You can post a sample using the XL2BB add-in.

But perhaps something like this?

	A	B	C	D	E	F	G
1
2		Today	2 Sep 2022
3
4		Date 1			Review date	Reviewed?
5		20 Nov 2021			20 May 2022	Yes
6		15 Dec 2021			14 Jun 2022
7		12 Jan 2022			12 Jul 2022	Yes
8		13 Feb 2022			13 Aug 2022
9		1 Mar 2022			29 Aug 2022
10		12 Apr 2022			10 Oct 2022
11		2 May 2022			30 Oct 2022
12
Sheet1

Cell Formulas
Range	Formula
C2	C2	=TODAY()
E5:E11	E5	=B5+181

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:F11	Expression	=$F5="Yes"	text	NO
B5:F11	Expression	=$E5<TODAY()	text	NO

 

[Jennifer Van]

Thank you, this worked
Jen