Conditional formatting to colour cell based on text in another

[Closed]
Report
Posts
1
Registration date
Monday December 16, 2013
Status
Member
Last seen
December 16, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hi,

I have a workbook tracking submission of work ideas. In column C I have the date received. In column J I have status (approved, rejected, pending, new). Column J has a drop down to choose status based on a list I created.

Currently I have a formula in Column C (which starts from row 8) to make any date older than 2 weeks appear in red. This was done by conditional formatting:

=$C8<=TODAY()-14

However, for those approved or rejected ideas, I don't need to track the date (i.e. don't want it to be coloured red). How do I add another condition?

My guess is something like adding an 'IF' function? But do I need to add another rule, as the formatting is different? (i.e. white, not red colour background)?

And would the formula be something like
=IF($J8="Approved","Rejected") and then format to white?

Thanks,

1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Nicm,

You could use AND:
=AND($C8<=TODAY()-14,$J8<>"Approved",$J8<>"Rejected")

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month