change formatting colours when bill is paid by tick box

[cazzalinko]

hello


please can someone guide this excel novice through formatting my excel "bills to be paid" template?


i am really finding it hard to make the data "auto format" so that overdue bills are red and "under"due bills are green and paid bills are blue (i cant work out how to make the date irrelevant if the bill is paid)


so far in my quest for ease of use i have an autoformatted table (meaning its pretty) but i really need to make the older bills stand out.


can i make a tick box that he can tick when he has paid a bill?

that would then change the entire row colour to blue?(blue meaning paid)


can a tick box do all this?


please let me know if you need to see my work (an how i could show it to you?) or any other clarifications that might be needed.


thanks so much! i feel that the forum leaders here may be overqualified but please eandevour to use lay mans terms if possibe

 

[Hui]

Cazzalinko


Firstly, Welcome to the Chandoo.org Forums


Your problem can all be solved using Conditional Formattting


have a read of

http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

&

http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/


or


Post your file somewhere for more specific advice

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[cazzalinko]

https://docs.google.com/leaf?id=1t19hSluFjFsjv5fqPVHhd9ttgMDIeELy--Q-eRx1RqI&hl=en_US

 

[cazzalinko]

thanks for a quick reply. i have read a lot of the advice ere for conditional formatting i just dont know where to apply formulas to what


or what formulas to use!

 

[Hui]

Select A2:K19 and remove all Conditional Formatting (CF)


Add 3 new CF's


=$G2<>0

set Blue

Stop if True = True


=and($F2<today(),$F2,<>"")

set Red

Stop if True = True


=and($F2>=today(),$F2,<>"")

set Green

Stop if True = False


arrange the CF's to be in the above order on the CF manage dialog

Apply

 

[cazzalinko]

i really am trying but it keeps saying these formulas are invalid? which rule type should i be selecting? ive been trying formula to determine but nothings working. also does set green mean format in rule formatting to turn text green? and where do i use stop?

 

[Hui]

They are CF by Formula

Have a look at https://rapidshare.com/files/775735805/contech_bills_due_july-sept.xlsx