Excel - Conditional formatting excluding empty cells

Asked By Kicke on 12-Sep-08 03:37 PM
This may be a somple question for all of you.  I want to use conditional
formatting to change fill color that specifically excludes blank cells.

The question is:  What value to I put in the condition/formula field of the
dialog box for a blank/empty cell.




T. Valko replied on 12-Sep-08 03:46 PM
Need more info.

What cells do you want to format and based on what condition?

--
Biff
Microsoft Excel MVP
Kicke replied on 12-Sep-08 04:04 PM
I have a collumn that shows project status with possible values of:

Started
,25
.50
.75
Complete
Stalled
Not Started

What I am trying to do is to get any cell where the value is NOT "Complete",
rest of the cells are default color.

I'd be happy to get anything that is not "Complete" and blank left as
default and the rest as colored.  When I try to do this using conditional
formatting it always ends up with the blank cells shaded along with the ones
I do want shaded.

Thanks
sameoldcalvin replied on 13-Sep-08 02:15 PM
use a formula to determine which cells to format --> an option in
conditional format

in the formula, use  =isblank (A1)

when the formula return true, the selected format will be
applied....and that should highlight the blank cells
T. Valko replied on 12-Sep-08 04:49 PM
Try this...

In Excel 2007

Select the range of cells. Assume this range is A1:A7
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=AND(A1<>"",A1<>"complete",A1<>"not started",A1<>"stalled")
Click the Format button
Select the desired style(s)
OK out

In Excel versions 2003 and earlier

Select the range of cells. Assume this range is A1:A7
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1<>"",A1<>"complete",A1<>"not started",A1<>"stalled")
Click the Format button
Select the desired style(s)
OK out


--
Biff
Microsoft Excel MVP
Rick Rothstein replied on 13-Sep-08 12:51 PM
Assuming that comma in front of your first item was supposed to be a dot,
and trying to read into your example data coupled with your text write up
the best I can, are you looking to highlight all the numerical values in
your column and leave everything else alone? If so, try this Conditional
Format formula...

=ISNUMBER(A1)

--
Rick (MVP - Excel)