Help regarding formatting of cells

[zain_shaikh]

Hello friends,

I am a novice excel user in need of some guidance regarding conditional formatting of cells. I am an accountant at a firm dealing in both cash & installment sales of certain products. For installment sales I have account forms for my clients which includes columns for due dates, receiving date, remaining balance etc. I am trying to format my column for due dates (starting from B13 going down to B34 or B35 according to sales terms of 12 months or 24 months) in such a way that the cell containing upcoming due date highlights it self blue, if any payment entered in a corresponding cell like E13 (in my case starting from E13 & so on) then B13 should highlight itself green. If the due date in lets say cell B15 is over due (according to date on my pc) & no payment is entered in E15 then it should highlight it self red.

I am trying hard to get some results but cannot get it.
I am attaching an example file for better understanding.

[FDibbins]

See if this will get you headed in the right direction?

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =AND($B13<TODAY(),$E13="") Format Fill Red

You now just need to create a new rule for the each of the other 2 conditions - shout if you need more help?

[zain_shaikh]

Hello FDibbins,

Your reply was extremely helpful, I got it working for over due (highlighted red) & for upcoming due dates (highlighted in blue). Em just stuck a little bit on 3rd condition which is change of cell color from red to green if I enter any payment in column E. For e.g I have an over due date (30-Jan-2015) & I receive payment today, after entering the payment I want that cell turn green. I also want to know if there is a way to count the number cells specifically highlighted in red.

Thanks in advance ..

[FDibbins]

Fir the green rule, try this...
=AND($B13<TODAY(),$E13<>"")

For the count (formulas dont count colors), try this...
=COUNTIFS($B$12:$B$47,"<"&TODAY(),$E$12:$E$47,"")

[zain_shaikh]

Hello FDibbins,

I checked out the formula, its working great just like I wanted. I am extremely grateful to you for your time my friend. You saved me from long hours of work. Once again, thank you for your guidance ..