Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Ref. in Formula changes automatically | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How to change a formula in one cell and have these changes appear | Excel Worksheet Functions | |||
Cannot change a formula | Excel Discussion (Misc queries) | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) |