formula help to change row colour automatically by some conditions
 

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

formula help to change row colour automatically by some conditions
 

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


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=523505

formula help to change row colour automatically by some conditions
 

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.

"Aarif" wrote in message
...
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

formula help to change row colour automatically by some condit
 

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

"AndyB" wrote:

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.

"Aarif" wrote in message
...
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

formula help to change row colour automatically by some condit
 

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.

"Aarif" wrote in message
...
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

"AndyB" wrote:

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.

"Aarif" wrote in message
...
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

formula help to change row colour automatically by some condit
 

Thank you very much Andy,

Aarif

"Andy" wrote:

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.

"Aarif" wrote in message
...
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

"AndyB" wrote:

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.

"Aarif" wrote in message
...
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

formula help to change row colour automatically by some condit
 

It's OK! Thanks for the feedback.

"Aarif" wrote in message
...
Thank you very much Andy,

Aarif

"Andy" wrote:

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.

"Aarif" wrote in message
...
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

"AndyB" wrote:

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.

"Aarif" wrote in message
...
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