Conditional Formatting If Other Column Contains Specific Text

[Ampharos]

Hi all,

My column C contains "Graduation Dates" and D contains Call Status (whether I have called). I want my conditional formatting to highlight my column C cells based on the following ideas:

• No formatting if date is far out
• Yellow for date is next week so I know I'll need to call soon
• Red for if date is today or in the past meaning I must call
• Green if I have called them meaning the task is done. This should have priority over the others (e.g., if a date is in the past but I called, it should be green).

I can't figure out the last one. I want C5 (the date 3/21/22) to be green because column D status says "3. Called - Contacted". I guess in this case I called early. What should the formula be in my conditional formatting manager?

Thank you so much in advance!

 

[jtakw]

Hi,

What are the different phrases you use in the "Call Status" column ?

Also, can you show some samples with expected results ?

 

[Ampharos]

Hi,

What are the different phrases you use in the "Call Status" column ?

Also, can you show some samples with expected results ?

Thanks jtakw.

There are three statuses via data validation list:
1. Not Called, 2. Called - VM, 3. Called - Contacted

The result should look like the attached (I deleted cond formatting, formatted manually).

 

[jtakw]

Are "Called - VM" and "Called - Contacted" Both considered task is done, therefore, Green ?

 

[Ampharos]

Are "Called - VM" and "Called - Contacted" Both considered task is done, therefore, Green ?

No, only if "3. Called - Contacted" would be considered complete and should be green.

 

[jtakw]

Thanks for clarifying.

3 CF Formulas, one for Each color.

Make sure, after you enter all three CF conditions, the condition for GREEN is top of the list in CF

Book3.xlsx
	C	D
1	Grad Date	Call Status
2	3/21/2022	3. Called - Contacted
3	3/16/2022	1. Not Called
4	3/19/2022	3. Called - Contacted
5	3/27/2022
6	3/26/2022
Sheet1053

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C6	Expression	=LEFT(D2)="3"	text	NO
C2:C6	Expression	=C2<=TODAY()	text	NO
C2:C6	Expression	=C2<=TODAY()+7	text	NO

 

[Ampharos]

Thanks for clarifying.

3 CF Formulas, one for Each color.

Make sure, after you enter all three CF conditions, the condition for GREEN is top of the list in CF

Book3.xlsx
	C	D
1	Grad Date	Call Status
2	3/21/2022	3. Called - Contacted
3	3/16/2022	1. Not Called
4	3/19/2022	3. Called - Contacted
5	3/27/2022
6	3/26/2022
Sheet1053

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C6	Expression	=LEFT(D2)="3"	text	NO
C2:C6	Expression	=C2<=TODAY()	text	NO
C2:C6	Expression	=C2<=TODAY()+7	text	NO

Thanks for your help, this was the solution! It was an easier formula than what I was using and also highlighted my priority (order of the rules in the manager) was incorrect.

 

[jtakw]

You're welcome, thanks for the feedback.