Conditional formatting help!!

[lisaspencer]

I have a table with a "DATE" column that I generally update daily with the current date, so my current conditional formatting for that column is - Cell Value < TODAY() - (with a green colour), so if the date is from yesterday or beyond then it will highlight green until I change it. But I have 2 other columns in the table; "REJECTIONS" and "GHOSTED", and if there's data or ("Y") I want the data to override the "DATE" formatting. So I want the "DATE" column to still have the highlighted green formats when the date is older than today, unless there is a 'Y' in the "REJECTIONS" and/or "GHOSTED" columns corresponding with the same row. If there is a 'Y' in the "REJECTIONS" column, then I want the date in the "DATE" column to be a different colour again. Is there a way to do an IF double formatting equation? I've attached some images to show what I mean, the second picture of the table with the dates highlighted dark pink is what I'm going for (obviously formatted manually just to show what I want through an equation).

 

[Toadstool]

Hi LisaSpencer,

So you have three rules and their priority is:

1. If Y in Rejection then red.
2. If Y in Ghosted then no format
3. If Date before today then green

You can select "Stop if true" on each conditional format check so if you have them in the above sequence it will work.

LisaSpencer.xlsx
	D	E	F	G	H	I	J	K	L
1	Last Message	W	B	A	S	T	N	G	R
2	15-Jul-20							Y
3	16-Jul-20								Y
4	17-Jul-20							Y	Y
5	18-Jul-20							Y
6	19-Jul-20							Y
7	18-Jul-20
8	18-Jul-20							Y	Y
9	18-Jul-20
10	23-Jul-20							Y
11	24-Jul-20								Y
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D11	Expression	=L2="Y"	text	YES
D2:D11	Expression	=K2="Y"	text	YES
D2:D11	Expression	=D2<TODAY()	text	YES

 

[lisaspencer]

GENIUS OVER HERE, thanks so much!!!!!