|
Conditional Formatting - more than 3?
Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
Depends on your conditions. Do you need more than three formats, or do
you need more than three conditions? Please post the conditions. |
Conditional Formatting - more than 3?
Hi Dave I have four ranges: 0 to 9999 10,000 to 19,999 20,000 to 29,999 and 30,000 to 39,999 on an invoice spreadsheet. If the value of an invoice falls within one of these ranges i want it to shade the cell a different colour. thanks -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
Hello penri0_0, Apparently we have the same problem. Maybe you can learn something from my posting he http://www.excelforum.com/showthread.php?t=542791 Succes! -- Lucas Lehmer ------------------------------------------------------------------------ Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
Thanks Lucas, i'll give it a go although i've got little experience programming! :rolleyes: -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
The only thing I know you can do is format the cell to your first condition
under Format Cells the conditionally format for the other three. Here is a link posted here before but I never tried. http://www.xldynamic.com/source/xld.....Download.html TC "penri0_0" wrote: Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
If you format all the cells with a base colour before adding the conditional
formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
Hi Saruman,
Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
Amy
The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
Ok. But, how do I apply this "default" to a column that already has
conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 |
Conditional Formatting - more than 3?
I do not know which 3 conditions you have used, but assuming Green, Red and
Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
Happy to hear you're sorted out.
Thanks for the feedback. On Thu, 20 Jul 2006 11:40:02 -0700, Amy wrote: Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value, the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
To clarify, the settings are as follows
condition 1: cell value is less than or equal to then green condition 2: cell value is between 1 and 1.25 then yellow condition 3: cell value is greater or equal to 1.25 then red For some reason when the cell vallue is "NA" (without the quotes) condition 3 is applied. "Amy" wrote: Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value, the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
Actually, its not resolved. In my reply just before yours, I outlined the
setitngs that I am using. For some reason the deafult base color isn't being applied. A. "Gord Dibben" wrote: Happy to hear you're sorted out. Thanks for the feedback. On Thu, 20 Jul 2006 11:40:02 -0700, Amy wrote: Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value, the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
How about you change condition 3 to "not equal to" NA
Anything over 1.25 will show red. Note: condition 2 colors 1.25 yellow. Excel seems to think between 1 and 1.25 includes 1.25 Gord On Thu, 20 Jul 2006 11:52:01 -0700, Amy wrote: To clarify, the settings are as follows condition 1: cell value is less than or equal to then green condition 2: cell value is between 1 and 1.25 then yellow condition 3: cell value is greater or equal to 1.25 then red For some reason when the cell vallue is "NA" (without the quotes) condition 3 is applied. "Amy" wrote: Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value, the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
Didn't work.
I changed the formula to enter nothing in the cell ("0" would be misleading) and reset the conditional formatting to Condition 1 cell value is between .01 and 1 then green Condition 2 cell value is between 1.01 and 1.25 then yellow Condition 3 cell value is greater than 1.25 then red But the empty cells are still getting red. (no pun intended!) "Gord Dibben" wrote: How about you change condition 3 to "not equal to" NA Anything over 1.25 will show red. Note: condition 2 colors 1.25 yellow. Excel seems to think between 1 and 1.25 includes 1.25 Gord On Thu, 20 Jul 2006 11:52:01 -0700, Amy wrote: To clarify, the settings are as follows condition 1: cell value is less than or equal to then green condition 2: cell value is between 1 and 1.25 then yellow condition 3: cell value is greater or equal to 1.25 then red For some reason when the cell vallue is "NA" (without the quotes) condition 3 is applied. "Amy" wrote: Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value, the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
If the formula in the cell returns a number or "" then go with this.
1 cell value is between .01 and 1 then green 2. cell value is between 1.01 and 1.25 then yellow 3. formula is =cellref<"" then red Note: what color do you want if the value is less than .1 As it stands, it will be red. I'm running out of ideas.......your parameters keep changing from one post to the next. Probably because I keep changing the CF entries<g Gord On Thu, 20 Jul 2006 12:55:02 -0700, Amy wrote: Didn't work. I changed the formula to enter nothing in the cell ("0" would be misleading) and reset the conditional formatting to Condition 1 cell value is between .01 and 1 then green Condition 2 cell value is between 1.01 and 1.25 then yellow Condition 3 cell value is greater than 1.25 then red But the empty cells are still getting red. (no pun intended!) "Gord Dibben" wrote: How about you change condition 3 to "not equal to" NA Anything over 1.25 will show red. Note: condition 2 colors 1.25 yellow. Excel seems to think between 1 and 1.25 includes 1.25 Gord On Thu, 20 Jul 2006 11:52:01 -0700, Amy wrote: To clarify, the settings are as follows condition 1: cell value is less than or equal to then green condition 2: cell value is between 1 and 1.25 then yellow condition 3: cell value is greater or equal to 1.25 then red For some reason when the cell vallue is "NA" (without the quotes) condition 3 is applied. "Amy" wrote: Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value, the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
Conditional Formatting - more than 3?
I'll give that a try. Many thanks for your help and time, Gord.
"Gord Dibben" wrote: If the formula in the cell returns a number or "" then go with this. 1 cell value is between .01 and 1 then green 2. cell value is between 1.01 and 1.25 then yellow 3. formula is =cellref<"" then red Note: what color do you want if the value is less than .1 As it stands, it will be red. I'm running out of ideas.......your parameters keep changing from one post to the next. Probably because I keep changing the CF entries<g Gord On Thu, 20 Jul 2006 12:55:02 -0700, Amy wrote: Didn't work. I changed the formula to enter nothing in the cell ("0" would be misleading) and reset the conditional formatting to Condition 1 cell value is between .01 and 1 then green Condition 2 cell value is between 1.01 and 1.25 then yellow Condition 3 cell value is greater than 1.25 then red But the empty cells are still getting red. (no pun intended!) "Gord Dibben" wrote: How about you change condition 3 to "not equal to" NA Anything over 1.25 will show red. Note: condition 2 colors 1.25 yellow. Excel seems to think between 1 and 1.25 includes 1.25 Gord On Thu, 20 Jul 2006 11:52:01 -0700, Amy wrote: To clarify, the settings are as follows condition 1: cell value is less than or equal to then green condition 2: cell value is between 1 and 1.25 then yellow condition 3: cell value is greater or equal to 1.25 then red For some reason when the cell vallue is "NA" (without the quotes) condition 3 is applied. "Amy" wrote: Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value, the cell changed to its default base color, which was none. But, when I applied the template to a new report, for some reason when "NA" was entered, the conditional formatting applied the red color, that was meant only for values that were above 1.25. Hmm. Thanks for the clarification. "Gord Dibben" wrote: I do not know which 3 conditions you have used, but assuming Green, Red and Yellow for the first 3 conditions............ Just don't format if "NA" Gord On Thu, 20 Jul 2006 11:02:02 -0700, Amy wrote: Ok. But, how do I apply this "default" to a column that already has conditional formatting set? Since the three conditions are already set, where or how to tell the established formatting to us no color if it meets none of these conditions? Thanks for replying! "Gord Dibben" wrote: Amy The fourth is the default "no color". Gord Dibben MS Excel MVP On Thu, 20 Jul 2006 09:55:02 -0700, Amy wrote: Hi Saruman, Could you explain how to format the cell with the base colour? Not sure what you mean? I need four colors. I have already green, yellow, and red. I just need one more color which would be white. 1. if it is less than 1 (100%) then green 2. if it is greater than 1 but less than 1.25 then yellow 3. if it is greater than 1.25 then red 4. if it is "NA" then white I apply the conditional formatting to a single column that has a forumla in each cell. Please advise. Thanks! Amy "Saruman" wrote: If you format all the cells with a base colour before adding the conditional formats then you will have 4 colours available Saruman ---------------------------------------------------------------------------- --------- All Outgoing Mail Checked With Norton Antivirus 2003 ---------------------------------------------------------------------------- --------- "penri0_0" wrote in message ... Hi, Is there any way i can format a cell with more than 3 conditions? Thanks in advance -- penri0_0 ------------------------------------------------------------------------ penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947 View this thread: http://www.excelforum.com/showthread...hreadid=542559 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
| All times are GMT +1. The time now is 08:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com