How to change a cell font red if any previous cell is red (thru conditional formatting)

[kattento]

Completely green Excel user.
I am trying to get column Z date to conditional format to red, if any previous dates in columns I through X are red. Those change to red based on conditional formatting. I am only needing column Z to change if the larger bolded date is red, not the smaller date (even if it's red).
Help!

working copy.xlsx
	A	B		D	E		G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
3	Scheduled Dig Date	Homeowner		Lot	Sales Rep		Plan / Elevation		Cntr Date		Design Completed		Lender	Loan Request Sent		Drafting Completed		Engineered / Stamped		Submit to City		Permit Picked Up		Loan Funded		Scheduled Dig Date	Actual Dig Date		Project Manager	Cris/ Sara
4	10/15	QMI		WM 53			Hemingway - C		8/12		8/19		Alta	8/23		9/2		9/9		9/14		10/12		10/14		10/15	10/19		Brandon	Sara
5									7/27		8/5			9/13		8/30		9/15		9/15		10/12
NON CONTINGENT

Cell Formulas
Range	Formula
A4	A4	=Z4
I4,P4	I4	=K4-7
K4	K4	=P4-14
N4	N4	=Z4-53
R4	R4	=T4-5
T4	T4	=V4-28
V4	V4	=Z4-3
X4	X4	=Z4-1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V4	Expression	=AND((V5=""),(V4<TODAY()))	text	NO
I4,K4,N4,P4,R4,T4,X4	Expression	=AND((I5=""),(I4<TODAY()))	text	NO
I5,K5,N5,P5,R5,T5,V5,X5	Expression	=I5>I4	text	NO

 

[Toadstool]

Hi Kattento,

Conditional Formatting can't check the colour of a cell so you can just OR the three previous checks into a new check:

=OR(AND((V5=""),(V4<TODAY())),(AND((I5=""),(I4<TODAY()))),(I5>I4))

 

[kattento]

Hi Kattento,

Conditional Formatting can't check the colour of a cell so you can just OR the three previous checks into a new check:

=OR(AND((V5=""),(V4<TODAY())),(AND((I5=""),(I4<TODAY()))),(I5>I4))

Ok... I tried the formula, I see what it's doing. The true/false will not work for what I need however. How can I change that? It would need to say "OK" if any previous cell is NOT red, and "ISSUE" if there are no previous red cells.

 

[Toadstool]

"It would need to say "OK" if any previous cell is NOT red, and "ISSUE" if there are no previous red cells."
I'm not following as your example column Z contains a date, not "OK" or "ISSUE".

As I previously said, Conditional Formatting cannot check the colour of a cell and neither can a Function; only VBA can do that. What Conditional Formatting can do is use the same rules as your existing CF. I think I see the issue now as the AND((I5=""),(I4<TODAY())) is applied to several columns so it would need to check each. Sadly the columns are not consistent (some 1 column apart, others 2) so it's a lengthy formula. Does this do what you want in the CF for Z4?

=OR(AND((V5=""),(V4<TODAY())),(AND((I5=""),(I4<TODAY()))),(AND((K5=""),(K4<TODAY()))),(AND((N5=""),(N4<TODAY()))),(AND((P5=""),(P4<TODAY()))),(AND((R5=""),(R4<TODAY()))),(AND((T5=""),(T4<TODAY()))),(AND((X5=""),(X4<TODAY()))))