Conditional Formatting….. Manage Rules

Hello!! First off, I just want to say thank you to everyone that looks at my post and I think this is a great site that I have visited many times! Thank you!

Overview:
In the sheet "Cost of Goods" that is part of our monthly budget for our business, each line item consist of a expense that can be categorized as a "Cost of Good". When we receive an invoice that belongs in the "Cost of Goods" sheet, we enter in the pertinent information like the Company Name, Account #, Invoice #, Amount Due, Date Due, and how the invoice will be paid from. At this Stage, we make the whole row yellow by using the Fill Color tool. Once the amount of the invoice has been deducted from our account, we enter the same amount that was in the Amount Due column in to the Amount Paid column and then we change the row from yellow to green. We do this because it helps us to easily differentiate between the invoices were the funds have been deducted from our account and the invoices that haven't been deducted from our account.

Problem:
I can't figure out how to create a rule in Conditional Formatting… -> Manage Rules were once we enter in a value into the column "Amount Due" that it turns that specific row yellow and then when we enter in that exact value into the column "Amount Paid" it then turns that specific row green.

Any Suggestions? I have attached an excel file that has two "Cost of Goods" sheets. One is the current sheet with no rows with any Fill Color and the other sheet, "goal" that show what we want the sheet to look if we were able to successful create a rule to do what we are trying to do.

One thing to note, sometimes we enter in expenses that we haven't yet received the invoice for because it is a monthly expense. In these cases we leave the row for that specific invoice/expense white.

Example.xlsx

1. For Amount Due: Highlight the section that you want the conditional formatting to apply to.
2. Select conditional formatting button.
3. Select classic for the style, I do this because that is what I am familiar with.
4. Select “use formula to determine cells to format”.
5. Enter this formula into the formula section =indirect(“U”&ROW())>0
6. Select formatting option, yellow fill and black font ( you may have to select custom format.
7. Select ok till it updates the sheet
8. Repeat steps 1 through 7 for Amount paid except for on step 5 enter this formula =indirect(“V”&ROW())>0.
9. You will need to click the plus sign to add a new condition
Hope this helps (see your attached updated file)

This helps a lot! Thank you!

What if I wanted the second formatting Rule to be when the value in Column U is equal to the Value in Column V of the same row then the row is Green would the formulate look something like these two attempts?

=indirect(“U”&ROW())=indirect(“V”&ROW()) ????

=indirect(“U”&ROW())=indirect(“V”&ROW()) ???

Neither of them work and also something weird happens. The Edit Formatting Rule Menu looks correct and the Fill and Font are what I set them to be but then when I click okay the Manage Rule Menu changes the FIll and Font to Yellow and Black, just like the second rule. See images below. Any thoughts?


Screen Shot 2014-01-15 at 2.38.59 PM.pngScreen Shot 2014-01-15 at 2.39.09 PM.png

What all rows 5 thru 22 are active would the formula then be =$u5:u22=$v5:v22 ??

I can't get that to work. I am trying to get it so the row fill color is yellow when the value in column u is greater then zero and the value in column V of the same row is zero. And then when the values of both U and V of the same row are equal and not zero then the row fill color is Green.

Could you create an if then formula that would update the fill color to yellow like:
=indirect(“U”&ROW())>0 & (“V”&ROW())=0 ->>> Yellow

=indirect(“U”&ROW())=(“V”&ROW()) & (“U”&ROW())>0 ->> Green

Okay, because you are U5 does them I have to create the rule for each row? If not what would the formula be for all the rows?

So in the formula Section:

=indirect(“U”&ROW())>0=AND(“U”&ROW())>0,(“V”&ROW())=0) ->> Yellow

=indirect(“V”&ROW())>0=AND((“U”&ROW())=(“V”&ROW()),((“U”&ROW())<>0) ->> Green

Crazy, the formula was shorten as it got more complicated!

Thanks for your help! What does INDIRECT do?