Conditional formatting to colour cell based on text in another

Closed
nicm123 Posts 1 Registration date Monday December 16, 2013 Status Member Last seen December 16, 2013 - Dec 16, 2013 at 06:25 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 19, 2013 at 11:06 AM
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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 19, 2013 at 11:06 AM
Hi Nicm,

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

Best regards,
Trowa
1