conditional formatting help

[Guy Pepper]

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...

[abousetta]

Hello and welcome to the forum. Please take a few minutes to read the forum rules and ammend your title. What you are after is simple using Conditional Formatting. You can set up three rules and they will color code the rows as the rules are fullfilled. If you have difficulty implementing, please post a sample workbook and we can show you how.

Hope this helps.

abousetta

P.S. Conditional Formatting is on the Home tab right in the middle with the red and blue colored cells.

[Pete_UK]

You would need to use Conditional Formatting to do this - as it implies, the format (colour) can be changed if some condition is met. In your case you have three conditions, but you can have many more than this in a cell.

However, I can't give you a fuller description as your title breaks the Forum rules - please change your title to something like Conditional Formatting Help, before the Moderators put a block on your thread.

Hope this helps.

Pete

[Pete_UK]

Select the cells from A13 to T13, click on the Conditional Formatting icon, click New Rule, and then in the Type box at the top select the bottom choice - Use a Formula etc. Then you should enter this formula in the box:

=$O13="Paid"

then click the Format button, click on the Fill tab and then choose your colour (yellow). Note it is important to enter the cell reference as $O13, so that all cells on that row will respond to the contents of the cell in column O. You should click OK twice to exit the dialogue box.

You can set up the other conditions and colours in a very similar way, and just set up the appropriate formulae each time.

Hope this helps.

Pete

[Guy Pepper]

Thanks Pete, I have done this but if i have to redo this for every cell it would take me AGES is there a way i am able to set it up so if the "paid" column comes up with a figure greater than $0 the rows will go yellow then do the same for "not due" and "overdue"? then can i repeat this for the entire spreadsheet easily? if possible add to the current formulas i have already so i can just drag it down??

thanks for your help

[Guy Pepper]

ok so what i have found is if i create this i get what i want to happen

Formula =$R4<10000000000000000

[Pete_UK]

Guy,

If you want to apply the conditional formatting to more rows, then you can use the Format Painter icon. With the CF set up in row 13, select all the cells in that row, then double-click the Format Painter icon. Then single-click in A14, A15, A16 etc progressively until you have done all the rows in your sheet, and then press the <Esc> key when your are finished.

If you are doing it from scratch then you could select all the cells from A13 to T-whatever first and then apply the CF rules to all those rows in one go - Excel will automatically adjust the formulae to suit each row (which is why you do not include the $ symbol in front of the row reference).

Hope this helps.

Pete