Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capture conditional format as cell format | Excel Discussion (Misc queries) | |||
copy conditional format without using format painter | Excel Worksheet Functions | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel |