How to create several rules to change a cell's format. [Closed]

Report
-
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
-
Hello,

I'm trying to create an excel spreadsheet to manage a property but I'm having difficulties in creating rules.

Here's the schedule I'm making
http://oi64.tinypic.com/2m6saaf.jpg

As you see, I have in H4 the date the cheque is due, and in I4 the status (a drop down list of either "Pending" or "Paid" )

I want a rule that says, If the date is overdue (from Today), and the status is Paid, the H4 shall turn Green, or even if the date is not overdue yet as long as the status is paid the H4 shall turn green.

Another rule that says, If the date is overdue, and the status is Pending, the H4 shall turn Red.
and the final rule says if the date is not overdue yet, and the status is pending, H4 shall turn Orange.

Appreciate your help!

1 reply

Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
136
eaqj, Good morning.

The Conditional Formatting will do this task for you.

Try to use:

Select H4
Conditional Format Menu --> Using Formula

Rule 1
=I4="Paid"
Format:Fill Color GREEN
OK

Rule 2
=AND(I4="PENDING", H4>=TODAY())
Format:Fill Color ORANGE
OK

Rule 1
=AND(I4="PENDING", H4<TODAY())
Format:Fill Color RED
OK

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!