software

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 amp; AB2 contains payment received amount
my formula in AA2 is =if(AB2=0,quot;Pendingquot;,if(AB2gt;=D2,quot;Collectedquot;,quot;Part
Paymentquot;))
if i am not receiving payment it shows pending
if i am receiving 700/- it shows part payment amp;
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


conditional format, check this out
www.mvps.org/dmcritchie/excel/condfmt.htm--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=523505Hi
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=quot;Partquot;
and select your Format. Then click on Add and use Formula Is and
=$AA2=quot;Completedquot;
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.

quot;Aarifquot; gt; wrote in message
...
gt; Hi Good People,
gt;
gt; I am working in excel worksheet for a payment collection activity and in
gt; culumn Ato Column Z i have detail of customer and in column AB i have
gt; amount
gt; of payment received and in column AA i made a condition by using if
gt; formula
gt; that will show me wheather payment is pending, collected or part payment
gt; ie.
gt;
gt; Column D2 having amount to be collected from customer is 1000/-
gt; my if furmula is in Column AA2 amp; AB2 contains payment received amount
gt; my formula in AA2 is =if(AB2=0,quot;Pendingquot;,if(AB2gt;=D2,quot;Collectedquot;,quot;Part
gt; Paymentquot;))
gt; if i am not receiving payment it shows pending
gt; if i am receiving 700/- it shows part payment amp;
gt; if i am receiving 1000/- it shows collected
gt;
gt; I want a formula that can change the whole row's colour when payment is
gt; collected or part payment done. I want two different colours for both
gt; because
gt; i have do follow ups for part payments to get remained amount. Can any
gt; give
gt; me a good solution for the same,
gt;
gt; Thanks in advance,
gt; Aarif
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

quot;AndyBquot; wrote:

gt; Hi
gt; What you want is Conditional Formatting (from the Format menu)
gt; Select your row (eg. row 2) and go to Format/Conditional Format.
gt; Change the option to Formula Is (from Cell Value) and in the box type
gt; =$AA2=quot;Partquot;
gt; and select your Format. Then click on Add and use Formula Is and
gt; =$AA2=quot;Completedquot;
gt; and select your other format.
gt; Once you are happy with this you can Format Paint it over all of the
gt; necessary rows.
gt;
gt; Hope this helps.
gt; Andy.
gt;
gt; quot;Aarifquot; gt; wrote in message
gt; ...
gt; gt; Hi Good People,
gt; gt;
gt; gt; I am working in excel worksheet for a payment collection activity and in
gt; gt; culumn Ato Column Z i have detail of customer and in column AB i have
gt; gt; amount
gt; gt; of payment received and in column AA i made a condition by using if
gt; gt; formula
gt; gt; that will show me wheather payment is pending, collected or part payment
gt; gt; ie.
gt; gt;
gt; gt; Column D2 having amount to be collected from customer is 1000/-
gt; gt; my if furmula is in Column AA2 amp; AB2 contains payment received amount
gt; gt; my formula in AA2 is =if(AB2=0,quot;Pendingquot;,if(AB2gt;=D2,quot;Collectedquot;,quot;Part
gt; gt; Paymentquot;))
gt; gt; if i am not receiving payment it shows pending
gt; gt; if i am receiving 700/- it shows part payment amp;
gt; gt; if i am receiving 1000/- it shows collected
gt; gt;
gt; gt; I want a formula that can change the whole row's colour when payment is
gt; gt; collected or part payment done. I want two different colours for both
gt; gt; because
gt; gt; i have do follow ups for part payments to get remained amount. Can any
gt; gt; give
gt; gt; me a good solution for the same,
gt; gt;
gt; gt; Thanks in advance,
gt; gt; Aarif
gt;
gt;
gt;

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.

quot;Aarifquot; gt; wrote in message
...
gt; Hi Andy,
gt; thank u very much for helping me i want to know one more thing ur formula
gt; is
gt; working fantastic but i have 10000 records in my sheet can i use this
gt; formula
gt; for entire sheet, if yes help me.
gt;
gt; Thanks again,
gt; Aarif
gt;
gt; quot;AndyBquot; wrote:
gt;
gt;gt; Hi
gt;gt; What you want is Conditional Formatting (from the Format menu)
gt;gt; Select your row (eg. row 2) and go to Format/Conditional Format.
gt;gt; Change the option to Formula Is (from Cell Value) and in the box type
gt;gt; =$AA2=quot;Partquot;
gt;gt; and select your Format. Then click on Add and use Formula Is and
gt;gt; =$AA2=quot;Completedquot;
gt;gt; and select your other format.
gt;gt; Once you are happy with this you can Format Paint it over all of the
gt;gt; necessary rows.
gt;gt;
gt;gt; Hope this helps.
gt;gt; Andy.
gt;gt;
gt;gt; quot;Aarifquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi Good People,
gt;gt; gt;
gt;gt; gt; I am working in excel worksheet for a payment collection activity and
gt;gt; gt; in
gt;gt; gt; culumn Ato Column Z i have detail of customer and in column AB i have
gt;gt; gt; amount
gt;gt; gt; of payment received and in column AA i made a condition by using if
gt;gt; gt; formula
gt;gt; gt; that will show me wheather payment is pending, collected or part
gt;gt; gt; payment
gt;gt; gt; ie.
gt;gt; gt;
gt;gt; gt; Column D2 having amount to be collected from customer is 1000/-
gt;gt; gt; my if furmula is in Column AA2 amp; AB2 contains payment received amount
gt;gt; gt; my formula in AA2 is =if(AB2=0,quot;Pendingquot;,if(AB2gt;=D2,quot;Collectedquot;,quot;Part
gt;gt; gt; Paymentquot;))
gt;gt; gt; if i am not receiving payment it shows pending
gt;gt; gt; if i am receiving 700/- it shows part payment amp;
gt;gt; gt; if i am receiving 1000/- it shows collected
gt;gt; gt;
gt;gt; gt; I want a formula that can change the whole row's colour when payment is
gt;gt; gt; collected or part payment done. I want two different colours for both
gt;gt; gt; because
gt;gt; gt; i have do follow ups for part payments to get remained amount. Can any
gt;gt; gt; give
gt;gt; gt; me a good solution for the same,
gt;gt; gt;
gt;gt; gt; Thanks in advance,
gt;gt; gt; Aarif
gt;gt;
gt;gt;
gt;gt;
Thank you very much Andy,

Aarif

quot;Andyquot; wrote:

gt; Yes, you can.
gt; When you Format Paint, just click on the Select All square (just to the left
gt; of column A and above row 1).
gt;
gt; Andy.
gt;
gt; quot;Aarifquot; gt; wrote in message
gt; ...
gt; gt; Hi Andy,
gt; gt; thank u very much for helping me i want to know one more thing ur formula
gt; gt; is
gt; gt; working fantastic but i have 10000 records in my sheet can i use this
gt; gt; formula
gt; gt; for entire sheet, if yes help me.
gt; gt;
gt; gt; Thanks again,
gt; gt; Aarif
gt; gt;
gt; gt; quot;AndyBquot; wrote:
gt; gt;
gt; gt;gt; Hi
gt; gt;gt; What you want is Conditional Formatting (from the Format menu)
gt; gt;gt; Select your row (eg. row 2) and go to Format/Conditional Format.
gt; gt;gt; Change the option to Formula Is (from Cell Value) and in the box type
gt; gt;gt; =$AA2=quot;Partquot;
gt; gt;gt; and select your Format. Then click on Add and use Formula Is and
gt; gt;gt; =$AA2=quot;Completedquot;
gt; gt;gt; and select your other format.
gt; gt;gt; Once you are happy with this you can Format Paint it over all of the
gt; gt;gt; necessary rows.
gt; gt;gt;
gt; gt;gt; Hope this helps.
gt; gt;gt; Andy.
gt; gt;gt;
gt; gt;gt; quot;Aarifquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Hi Good People,
gt; gt;gt; gt;
gt; gt;gt; gt; I am working in excel worksheet for a payment collection activity and
gt; gt;gt; gt; in
gt; gt;gt; gt; culumn Ato Column Z i have detail of customer and in column AB i have
gt; gt;gt; gt; amount
gt; gt;gt; gt; of payment received and in column AA i made a condition by using if
gt; gt;gt; gt; formula
gt; gt;gt; gt; that will show me wheather payment is pending, collected or part
gt; gt;gt; gt; payment
gt; gt;gt; gt; ie.
gt; gt;gt; gt;
gt; gt;gt; gt; Column D2 having amount to be collected from customer is 1000/-
gt; gt;gt; gt; my if furmula is in Column AA2 amp; AB2 contains payment received amount
gt; gt;gt; gt; my formula in AA2 is =if(AB2=0,quot;Pendingquot;,if(AB2gt;=D2,quot;Collectedquot;,quot;Part
gt; gt;gt; gt; Paymentquot;))
gt; gt;gt; gt; if i am not receiving payment it shows pending
gt; gt;gt; gt; if i am receiving 700/- it shows part payment amp;
gt; gt;gt; gt; if i am receiving 1000/- it shows collected
gt; gt;gt; gt;
gt; gt;gt; gt; I want a formula that can change the whole row's colour when payment is
gt; gt;gt; gt; collected or part payment done. I want two different colours for both
gt; gt;gt; gt; because
gt; gt;gt; gt; i have do follow ups for part payments to get remained amount. Can any
gt; gt;gt; gt; give
gt; gt;gt; gt; me a good solution for the same,
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks in advance,
gt; gt;gt; gt; Aarif
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

It's OK! Thanks for the feedback.

quot;Aarifquot; gt; wrote in message
...
gt; Thank you very much Andy,
gt;
gt; Aarif
gt;
gt; quot;Andyquot; wrote:
gt;
gt;gt; Yes, you can.
gt;gt; When you Format Paint, just click on the Select All square (just to the
gt;gt; left
gt;gt; of column A and above row 1).
gt;gt;
gt;gt; Andy.
gt;gt;
gt;gt; quot;Aarifquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi Andy,
gt;gt; gt; thank u very much for helping me i want to know one more thing ur
gt;gt; gt; formula
gt;gt; gt; is
gt;gt; gt; working fantastic but i have 10000 records in my sheet can i use this
gt;gt; gt; formula
gt;gt; gt; for entire sheet, if yes help me.
gt;gt; gt;
gt;gt; gt; Thanks again,
gt;gt; gt; Aarif
gt;gt; gt;
gt;gt; gt; quot;AndyBquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi
gt;gt; gt;gt; What you want is Conditional Formatting (from the Format menu)
gt;gt; gt;gt; Select your row (eg. row 2) and go to Format/Conditional Format.
gt;gt; gt;gt; Change the option to Formula Is (from Cell Value) and in the box type
gt;gt; gt;gt; =$AA2=quot;Partquot;
gt;gt; gt;gt; and select your Format. Then click on Add and use Formula Is and
gt;gt; gt;gt; =$AA2=quot;Completedquot;
gt;gt; gt;gt; and select your other format.
gt;gt; gt;gt; Once you are happy with this you can Format Paint it over all of the
gt;gt; gt;gt; necessary rows.
gt;gt; gt;gt;
gt;gt; gt;gt; Hope this helps.
gt;gt; gt;gt; Andy.
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Aarifquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; Hi Good People,
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I am working in excel worksheet for a payment collection activity
gt;gt; gt;gt; gt; and
gt;gt; gt;gt; gt; in
gt;gt; gt;gt; gt; culumn Ato Column Z i have detail of customer and in column AB i
gt;gt; gt;gt; gt; have
gt;gt; gt;gt; gt; amount
gt;gt; gt;gt; gt; of payment received and in column AA i made a condition by using if
gt;gt; gt;gt; gt; formula
gt;gt; gt;gt; gt; that will show me wheather payment is pending, collected or part
gt;gt; gt;gt; gt; payment
gt;gt; gt;gt; gt; ie.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Column D2 having amount to be collected from customer is 1000/-
gt;gt; gt;gt; gt; my if furmula is in Column AA2 amp; AB2 contains payment received
gt;gt; gt;gt; gt; amount
gt;gt; gt;gt; gt; my formula in AA2 is
gt;gt; gt;gt; gt; =if(AB2=0,quot;Pendingquot;,if(AB2gt;=D2,quot;Collectedquot;,quot;Part
gt;gt; gt;gt; gt; Paymentquot;))
gt;gt; gt;gt; gt; if i am not receiving payment it shows pending
gt;gt; gt;gt; gt; if i am receiving 700/- it shows part payment amp;
gt;gt; gt;gt; gt; if i am receiving 1000/- it shows collected
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I want a formula that can change the whole row's colour when payment
gt;gt; gt;gt; gt; is
gt;gt; gt;gt; gt; collected or part payment done. I want two different colours for
gt;gt; gt;gt; gt; both
gt;gt; gt;gt; gt; because
gt;gt; gt;gt; gt; i have do follow ups for part payments to get remained amount. Can
gt;gt; gt;gt; gt; any
gt;gt; gt;gt; gt; give
gt;gt; gt;gt; gt; me a good solution for the same,
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thanks in advance,
gt;gt; gt;gt; gt; Aarif
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;