Conditional formating - for blank cells

[pearce]

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.

 

[Rick Rothstein]

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.

 

[Gemini]

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

 

[Rick Rothstein]

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(A1>2,A1<5)

=AND(A1>4,A1<7)

 

[pearce]

Thank you, I've put the last set of formulae in and it's doneexactly what I
wanted it to do. Many thanks

 

[Gemini]

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