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

Closed
Eaqj - Oct 28, 2016 at 03:56 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Oct 28, 2016 at 07:16 AM
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 response

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 28, 2016 at 07:16 AM
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
0