Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating - for blank cells
I have a column that I want to use conditional formating on. Some cells are
blank. I want them to remain blank and un shaded. The other cells contain values between 0 and 6. I want values between 0-2 to turn red, 3-4 to turn yellow and 5-6 to turn green. How do I do this? So far I've managed to apply shading but the blank cells also turn red instead of staying clear. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating - for blank cells
Change your conditional format formula from this (generalize because you did
not post your actual formula)... =YourCondition to this... =AND(YourCondition,ActiveCell<"") where ActiveCell is the same cell reference that you have in YourCondition. -- Rick (MVP - Excel) "pearce" wrote in message ... I have a column that I want to use conditional formating on. Some cells are blank. I want them to remain blank and un shaded. The other cells contain values between 0 and 6. I want values between 0-2 to turn red, 3-4 to turn yellow and 5-6 to turn green. How do I do this? So far I've managed to apply shading but the blank cells also turn red instead of staying clear. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating - for blank cells
pearce, you haven't specified which version of Excel you're using. I use
Excel 2003, so you may need to adapt my suggestion if you use Office 2007. In the Conditional Formatting dialog box, instead of using "Cell Value Is", use "Formula Is". The dialog box allows for three conditions. Use the following, in that order. I'm using cell A1 in the example. =IF( AND( ( A1 < ""), ( A1 = 0 ), ( A1 < 3 ) ), true, false ) =IF( AND( ( A1 2 ), ( A1 < 5 ) ), true, false ) =IF( AND( ( A1 4 ), ( A1 < 7 ) ), true, false ) hth! -- Gemini "pearce" wrote: I have a column that I want to use conditional formating on. Some cells are blank. I want them to remain blank and un shaded. The other cells contain values between 0 and 6. I want values between 0-2 to turn red, 3-4 to turn yellow and 5-6 to turn green. How do I do this? So far I've managed to apply shading but the blank cells also turn red instead of staying clear. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating - for blank cells
You don't need the IF function call to produce a TRUE/FALSE result since the
output from the AND function call is already TRUE/FALSE (what you have is like saying If TRUE then return TRUE else if FALSE return FALSE... since you already have TRUE or FALSE, just return them directly). Also, you do not need to surround each logical expression with parentheses (the commas serve as sufficient delimiters). Your three post formulas can be reduced to these... =AND(A1<"",A1=0,A1<3) =AND(A12,A1<5) =AND(A14,A1<7) -- Rick (MVP - Excel) "Gemini" wrote in message ... pearce, you haven't specified which version of Excel you're using. I use Excel 2003, so you may need to adapt my suggestion if you use Office 2007. In the Conditional Formatting dialog box, instead of using "Cell Value Is", use "Formula Is". The dialog box allows for three conditions. Use the following, in that order. I'm using cell A1 in the example. =IF( AND( ( A1 < ""), ( A1 = 0 ), ( A1 < 3 ) ), true, false ) =IF( AND( ( A1 2 ), ( A1 < 5 ) ), true, false ) =IF( AND( ( A1 4 ), ( A1 < 7 ) ), true, false ) hth! -- Gemini "pearce" wrote: I have a column that I want to use conditional formating on. Some cells are blank. I want them to remain blank and un shaded. The other cells contain values between 0 and 6. I want values between 0-2 to turn red, 3-4 to turn yellow and 5-6 to turn green. How do I do this? So far I've managed to apply shading but the blank cells also turn red instead of staying clear. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating - for blank cells
Thank you, I've put the last set of formulae in and it's doneexactly what I
wanted it to do. Many thanks "Rick Rothstein" wrote: You don't need the IF function call to produce a TRUE/FALSE result since the output from the AND function call is already TRUE/FALSE (what you have is like saying If TRUE then return TRUE else if FALSE return FALSE... since you already have TRUE or FALSE, just return them directly). Also, you do not need to surround each logical expression with parentheses (the commas serve as sufficient delimiters). Your three post formulas can be reduced to these... =AND(A1<"",A1=0,A1<3) =AND(A12,A1<5) =AND(A14,A1<7) -- Rick (MVP - Excel) "Gemini" wrote in message ... pearce, you haven't specified which version of Excel you're using. I use Excel 2003, so you may need to adapt my suggestion if you use Office 2007. In the Conditional Formatting dialog box, instead of using "Cell Value Is", use "Formula Is". The dialog box allows for three conditions. Use the following, in that order. I'm using cell A1 in the example. =IF( AND( ( A1 < ""), ( A1 = 0 ), ( A1 < 3 ) ), true, false ) =IF( AND( ( A1 2 ), ( A1 < 5 ) ), true, false ) =IF( AND( ( A1 4 ), ( A1 < 7 ) ), true, false ) hth! -- Gemini "pearce" wrote: I have a column that I want to use conditional formating on. Some cells are blank. I want them to remain blank and un shaded. The other cells contain values between 0 and 6. I want values between 0-2 to turn red, 3-4 to turn yellow and 5-6 to turn green. How do I do this? So far I've managed to apply shading but the blank cells also turn red instead of staying clear. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formating - for blank cells
Rick, you're correct. Based on the OP, I wasn't quite sure how much he/she
was knew about the conditional formatting feature. Hence, I tried making it as clear as I could. I'm also well aware some of those parentheses aren't strictly necessary. However, it's been a long standing practice of mine to include those, since they make (to me) a formula easier to read. That stems from a habit I developed a long time ago in my software career. I guess old habits die hard. Hopefully, the longer version helped the OP understand the logic as well as catch the little error described in the initial post. -- Gemini "Rick Rothstein" wrote: You don't need the IF function call to produce a TRUE/FALSE result since the output from the AND function call is already TRUE/FALSE (what you have is like saying If TRUE then return TRUE else if FALSE return FALSE... since you already have TRUE or FALSE, just return them directly). Also, you do not need to surround each logical expression with parentheses (the commas serve as sufficient delimiters). Your three post formulas can be reduced to these... =AND(A1<"",A1=0,A1<3) =AND(A12,A1<5) =AND(A14,A1<7) -- Rick (MVP - Excel) "Gemini" wrote in message ... pearce, you haven't specified which version of Excel you're using. I use Excel 2003, so you may need to adapt my suggestion if you use Office 2007. In the Conditional Formatting dialog box, instead of using "Cell Value Is", use "Formula Is". The dialog box allows for three conditions. Use the following, in that order. I'm using cell A1 in the example. =IF( AND( ( A1 < ""), ( A1 = 0 ), ( A1 < 3 ) ), true, false ) =IF( AND( ( A1 2 ), ( A1 < 5 ) ), true, false ) =IF( AND( ( A1 4 ), ( A1 < 7 ) ), true, false ) hth! -- Gemini "pearce" wrote: I have a column that I want to use conditional formating on. Some cells are blank. I want them to remain blank and un shaded. The other cells contain values between 0 and 6. I want values between 0-2 to turn red, 3-4 to turn yellow and 5-6 to turn green. How do I do this? So far I've managed to apply shading but the blank cells also turn red instead of staying clear. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I refer to a blank cell when using conditional formating? | Excel Discussion (Misc queries) | |||
conditional formating for blank. | Excel Worksheet Functions | |||
Use conditional formating to incicate blank cells in excel | Excel Worksheet Functions | |||
If is blank= conditional formating | Excel Worksheet Functions | |||
conditional formating for a blank cell | Excel Discussion (Misc queries) |