conditional formatting help

I have been playing around with excel for a while now although I am still learning, I am a business owner and have setup an excel sheet i have been using for invoice generation logging jobs etc just wondering if I can get a little help with some parts of it i cannot work out.


I have setup these columns
Column O Q R S T
Paid/Not paid, Charged, Paid,Not Due, Overdue

So the Paid/Not paid column is just a drop down list to show if the customer has paid their bill.
the charged column is the price i have charged the customer
the paid column has:
=IF(O13="Paid",Q13,"")
the Not due column has:
=IF(D13=0,"",(IF(O13="Not Paid",IF(SUM(N13+24)>$AD$2,Q13,""),"")))
the overdue column has:
=IF(D13=0,"",(IF(O13="Not Paid",IF(SUM(N13+24)<$AD$2,Q13,""),"")))

Column D is to make sure i have the customers name
and $AD$2 is =Now() for the current date



hopefully i have explained this well enough so you understand whats going on.

What i would like to do is set up a Row color scheme to make unpaid or overdue bills easier to see.

so when the Paid column sees that the bill is paid rows A to T become Yellow

when the Not due column sees its not due yet rows A to T become blue

when the Overdue column sees its overdue rows A to T become red


I have also tried putting in a drop down list for the paid/notpaid column but my formulas don't work when i have done that any ideas why this would be or how to fix it?

the invoice numbers in column "C" are all hyper linked to folders is there a way to auto hyperlink i have seen you cant just drag to do it, all the invoice folders are labeled the same as in column "C"


thanks in advance for any help/advice on what i should do

i have attached the worksheet for you to have a look at...