A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Conditional formatting excluding empty cells



 
 
Thread Tools Display Modes
  #1  
Old September 12th 08, 08:37 PM posted to microsoft.public.excel.worksheet.functions
Kicker
external usenet poster
 
Posts: 3
Default Conditional formatting excluding empty cells

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.
Ads
  #2  
Old September 12th 08, 08:46 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default Conditional formatting excluding empty cells

Need more info.

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

--
Biff
Microsoft Excel MVP


"Kicker" > wrote in message
...
> 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.



  #3  
Old September 12th 08, 09:04 PM posted to microsoft.public.excel.worksheet.functions
Kicker
external usenet poster
 
Posts: 3
Default Conditional formatting excluding empty cells

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",
"Not Started", "Stalled" or blank to have a different background color. The
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

"T. Valko" wrote:

> Need more info.
>
> What cells do you want to format and based on what condition?
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Kicker" > wrote in message
> ...
> > 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.

>
>
>

  #4  
Old September 12th 08, 09:27 PM posted to microsoft.public.excel.worksheet.functions
sameoldcalvin
external usenet poster
 
Posts: 9
Default Conditional formatting excluding empty cells

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
  #5  
Old September 12th 08, 09:49 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default Conditional formatting excluding empty cells

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


"Kicker" > wrote in message
...
>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",
> "Not Started", "Stalled" or blank to have a different background color.
> The
> 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
>
> "T. Valko" wrote:
>
>> Need more info.
>>
>> What cells do you want to format and based on what condition?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Kicker" > wrote in message
>> ...
>> > 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.

>>
>>
>>



  #6  
Old September 13th 08, 05:51 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default Conditional formatting excluding empty cells

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)


"Kicker" > wrote in message
...
>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",
> "Not Started", "Stalled" or blank to have a different background color.
> The
> 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
>
> "T. Valko" wrote:
>
>> Need more info.
>>
>> What cells do you want to format and based on what condition?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Kicker" > wrote in message
>> ...
>> > 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.

>>
>>
>>


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting days comparing 2 dates excluding empty cells Terry Rogers[_2_] Excel Worksheet Functions 4 August 11th 08 04:27 PM
Conditional Formatting on empty cells mgccoop Excel Worksheet Functions 6 July 17th 08 07:54 PM
Conditional Formatting on empty cells mgccoop Excel Discussion (Misc queries) 6 July 4th 08 10:41 PM
lowest number in row excluding empty cells Mark Excel Discussion (Misc queries) 5 April 23rd 08 04:23 PM
excluding cells from the formula when empty Joe Shell Excel Worksheet Functions 5 November 21st 04 04:37 PM


All times are GMT +1. The time now is 03:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.