|
blank = no formating, <95% Red, 95% Green
I need to conditionally format cells based on their value. These values are
in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
1. Select the range. Suppose it is column F. The cell which is referenced in
the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
Another option to jacobs post is if you are only trying to format the text
colour then you could use a custom format as such... Format - Cells... - Number - Custom [Green][=0.95] 0.0%;[Red][<0.95] 0.0%;General -- HTH... Jim Thomlinson "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
Sorry Jacob. I did not capitalize your name... Purely unintentional I assure
you. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Another option to jacobs post is if you are only trying to format the text colour then you could use a custom format as such... Format - Cells... - Number - Custom [Green][=0.95] 0.0%;[Red][<0.95] 0.0%;General -- HTH... Jim Thomlinson "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
Good morning Jacob,
The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
The CF need to be specific to each cell. To that end you do not want absolute
referencing. Highlight Cells I2:I82 (with I2 as the active cell) Add this formula for the CF =AND(I2<"",I2=0.95) Green Do the same for Red. This is a relative referenced function so that it will be correctly referenced for each cell in the range selected. -- HTH... Jim Thomlinson "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
Conditional formula sets the formula to all cells in the selection
automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
Its working, but blank cells are green. Do I need to make a third condition with ="" ? "Jacob Skaria" wrote: Conditional formula sets the formula to all cells in the selection automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
You dont need a third condition...
Just the formula =I20.95 is enough OR =AND(I2<"",I2=0.95) If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Its working, but blank cells are green. Do I need to make a third condition with ="" ? "Jacob Skaria" wrote: Conditional formula sets the formula to all cells in the selection automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
Hi,
just change the 2nd condition to =AND(I2<"",I20.95) Formating is green "Go Bucks!!!" wrote: Its working, but blank cells are green. Do I need to make a third condition with ="" ? "Jacob Skaria" wrote: Conditional formula sets the formula to all cells in the selection automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
The second formula, =AND(I2<"",I2=0.95) worked.
If not, the blank cells turned green. Thank you, Steve "Jacob Skaria" wrote: You dont need a third condition... Just the formula =I20.95 is enough OR =AND(I2<"",I2=0.95) If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Its working, but blank cells are green. Do I need to make a third condition with ="" ? "Jacob Skaria" wrote: Conditional formula sets the formula to all cells in the selection automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
One more followup. I was hoping to apply these conditions only to selected
cells. I did so using a filter on Column I. But I see that the conditions were applied to all the cells in column I. My criteria will be different based on the customer noted in Column E. e.g. Customer A, 95 Customer B, 85 Customer C, 90 Thanks, "Jacob Skaria" wrote: You dont need a third condition... Just the formula =I20.95 is enough OR =AND(I2<"",I2=0.95) If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Its working, but blank cells are green. Do I need to make a third condition with ="" ? "Jacob Skaria" wrote: Conditional formula sets the formula to all cells in the selection automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
Blank cells would not turn green with the condition =I20.95
but you would get green if the cell contains a string, even the zero length string ="" -- David Biddulph "Go Bucks!!!" wrote in message ... The second formula, =AND(I2<"",I2=0.95) worked. If not, the blank cells turned green. Thank you, Steve "Jacob Skaria" wrote: You dont need a third condition... Just the formula =I20.95 is enough OR =AND(I2<"",I2=0.95) If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Its working, but blank cells are green. Do I need to make a third condition with ="" ? "Jacob Skaria" wrote: Conditional formula sets the formula to all cells in the selection automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
blank = no formating, <95% Red, 95% Green
I got my answer.
The formula is... =OR(AND(I2<"",E2=$K$5,I2=$O$5),AND(I2<"",E2=$K$ 6,I2=$O$6),AND(I2<"",E2=$K$7,I2=$O$7),AND(I2<" ",E2=$K$8,I2=$O$8),AND(I2<"",E2=$K$9,I2=$O$9),A ND(I2<"",E2=$K$10,I2=$O$10)) whe I has the data, E has the company name, and K and O are the company and performance target criteria. works great. I put the I2<"" into all formulas to keep from getting the green in blank cells. Thanks all! "Go Bucks!!!" wrote: One more followup. I was hoping to apply these conditions only to selected cells. I did so using a filter on Column I. But I see that the conditions were applied to all the cells in column I. My criteria will be different based on the customer noted in Column E. e.g. Customer A, 95 Customer B, 85 Customer C, 90 Thanks, "Jacob Skaria" wrote: You dont need a third condition... Just the formula =I20.95 is enough OR =AND(I2<"",I2=0.95) If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Its working, but blank cells are green. Do I need to make a third condition with ="" ? "Jacob Skaria" wrote: Conditional formula sets the formula to all cells in the selection automatically. You dont need to refer that in your formula. Just select the cells say I2:I82 and enter these formulas in CF conditions =AND(I2<"",I2<0.95) Formatting is Red =(I2=0.95) Formating is Green If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: Good morning Jacob, The result was that all cells turned green (including blanks and values below 95%). I am applying the condition to a selection, so the formula currently looks like this... =($I$2:$I$82=0.95) Formating is Green =AND($I$2:$I$82<"",$I$2:$I$82<0.95) Formatting is Red "Jacob Skaria" wrote: 1. Select the range. Suppose it is column F. The cell which is referenced in the formula is the active cell in the selection. Active cell will have the white background even after selection. 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(F1<"",F1<0.95) Click Format ButtonPattern and select your color (say Red) 4. Click on Add button. 5. For Condition2Select 'Formula Is' and enter the below formula =F1=0.95) Click Format ButtonPattern and select your color (say Green) 6. Hit OK If this post helps click Yes --------------- Jacob Skaria "Go Bucks!!!" wrote: I need to conditionally format cells based on their value. These values are in percent. I want the following conditional formating rules: blank = no formating, <95% Red, 95% Green Thanks, |
| All times are GMT +1. The time now is 08:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com