Conditional Format
I have several columns that I would like to apply Conditional Formating to
but exclude cells that have $0.00 in them. I would like to use a 3-color scale from lowest being green, mid being yellow and highest being red. and be able to copy it down the columns after it is created. Example: A B C D E F G H I J K L M N O P 7 Item1 X X 1.25 X X 0.00 X X 0.00 X X 1.00 X X 1.38 8 Item2 X X 1.25 X X 0.00 X X 0.00 X X 1.08 X X 1.50 9 Item3 X X 1.30 X X 0.00 X X 0.00 X X 1.20 X X 1.33 10 Item4 X X 1.85 X X 0.00 X X 0.00 X X 1.20 X X 1.50 Etc... I would like the conditional formating to apply per row and apply the color as long as it doesn't have a 0.00 in it and rank it from lowest to highest for cells D7,G7,J7,M7, and P7. Then I would like to copy that setup down the columns to work the same way. I have about 100 rows as it stands now with information and formulas already put together and I do not want to mess that up. Can anyone help? Thanks, Scott A |
Conditional Format
So, just to get this clear, your column D contains 1.25, 1.25, 1.30
and 1.85, and you would like the 1.85 to be red, the two 1.25 values to be green and the one in the middle to be yellow? And you want a similar format applied to columns G, J, M and P ? I assume that where you show X in your example you do not want CF to be applied. Is that correct? Pete On Nov 4, 12:36*am, Scott A wrote: I have several columns that I would like to apply Conditional Formating to but exclude cells that have $0.00 in them. *I would like to use a 3-color scale from lowest being green, mid being yellow and highest being red. and be able to copy it down the columns after it is created. Example: * * * * * A * * *B * *C * * D * * E * * F * * G * *H * *I * * * J * *K * * L * M * * N * *O * P 7 * Item1 * * X * *X * 1.25 * X * *X * 0.00 *X * *X * 0.00 *X * *X *1.00 *X * * X *1.38 8 * Item2 * * X * *X * 1.25 * X * *X * 0.00 *X * *X * 0.00 *X * *X *1.08 *X * * X *1.50 9 * Item3 * * X * *X * 1.30 * X * *X * 0.00 *X * *X * 0.00 *X * *X *1.20 *X * * X *1.33 10 Item4 * * X * *X * 1.85 * X * *X * 0.00 *X * *X * 0.00 *X * *X *1.20 *X * *X *1.50 Etc... I would like the conditional formating to apply per row and apply the color as long as it doesn't have a 0.00 in it and rank it from lowest to highest for cells D7,G7,J7,M7, and P7. *Then I would like to copy that setup down the columns to work the same way. *I have about 100 rows as it stands now with information and formulas already put together and I do not want to mess that up. Can anyone help? Thanks, Scott A |
Conditional Format
Select cells D7, G7, J7, M7, and P7, with D7 being the active cells.
Conditional format, formulas a =D7=MIN(IF((MOD(COLUMN($D7:$P7)-1,3)=0)*($D7:$P7<0),$D7:$P7)) =D7=MEDIAN(IF((MOD(COLUMN($D7:$P7)-1,3)=0)*($D7:$P7<0),$D7:$P7)) =D7=MAX(IF((MOD(COLUMN($D7:$P7)-1,3)=0)*($D7:$P7<0),$D7:$P7)) You can then copy these formats down as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scott A" wrote: I have several columns that I would like to apply Conditional Formating to but exclude cells that have $0.00 in them. I would like to use a 3-color scale from lowest being green, mid being yellow and highest being red. and be able to copy it down the columns after it is created. Example: A B C D E F G H I J K L M N O P 7 Item1 X X 1.25 X X 0.00 X X 0.00 X X 1.00 X X 1.38 8 Item2 X X 1.25 X X 0.00 X X 0.00 X X 1.08 X X 1.50 9 Item3 X X 1.30 X X 0.00 X X 0.00 X X 1.20 X X 1.33 10 Item4 X X 1.85 X X 0.00 X X 0.00 X X 1.20 X X 1.50 Etc... I would like the conditional formating to apply per row and apply the color as long as it doesn't have a 0.00 in it and rank it from lowest to highest for cells D7,G7,J7,M7, and P7. Then I would like to copy that setup down the columns to work the same way. I have about 100 rows as it stands now with information and formulas already put together and I do not want to mess that up. Can anyone help? Thanks, Scott A |
All times are GMT +1. The time now is 09:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com