formula help to change row colour automatically by some conditions

[Guest]

Hi Good People,

I am working in excel worksheet for a payment collection activity and in
culumn Ato Column Z i have detail of customer and in column AB i have amount
of payment received and in column AA i made a condition by using if formula
that will show me wheather payment is pending, collected or part payment ie.

Column D2 having amount to be collected from customer is 1000/-
my if furmula is in Column AA2 & AB2 contains payment received amount
my formula in AA2 is =if(AB2=0,"Pending",if(AB2>=D2,"Collected","Part
Payment"))
if i am not receiving payment it shows pending
if i am receiving 700/- it shows part payment &
if i am receiving 1000/- it shows collected

I want a formula that can change the whole row's colour when payment is
collected or part payment done. I want two different colours for both because
i have do follow ups for part payments to get remained amount. Can any give
me a good solution for the same,

Thanks in advance,
Aarif

 

[davesexcel]

conditional format, check this out
http://www.mvps.org/dmcritchie/excel/condfmt.ht

 

[Guest]

Hi
What you want is Conditional Formatting (from the Format menu)
Select your row (eg. row 2) and go to Format/Conditional Format.
Change the option to Formula Is (from Cell Value) and in the box type
=$AA2="Part"
and select your Format. Then click on Add and use Formula Is and
=$AA2="Completed"
and select your other format.
Once you are happy with this you can Format Paint it over all of the
necessary rows.

Hope this helps.
Andy.

 

[Guest]

Hi Andy,
thank u very much for helping me i want to know one more thing ur formula is
working fantastic but i have 10000 records in my sheet can i use this formula
for entire sheet, if yes help me.

Thanks again,
Aarif

 

[Guest]

Yes, you can.
When you Format Paint, just click on the Select All square (just to the left
of column A and above row 1).

Andy.

 

[Guest]

Thank you very much Andy,

Aarif

Yes, you can.
When you Format Paint, just click on the Select All square (just to the left
of column A and above row 1).

Andy.

 

[Guest]

It's OK! Thanks for the feedback.

Thank you very much Andy,

Aarif