Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Conditional Formatting on empty cells

I'm setting up a new report for the month of July and creating formatting
rules for data that will be entered throughout the month. However, when I
apply my rules to cells that have not yet got data entered (eg. for next
week) it is showing up as "formatted" even though the cell is empty. Is
there an override for this conditional formatting so that it the formatting
won't show up until I enter something in that cell? I'm new to Excel 2007.
Thanks,
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Conditional Formatting on empty cells

Add an argument to your formulas that tests for that condition.

For example, if you had a formula:

=A1<100

Revise it to:

=And(A1<100,A1<"")
OR
=And(A1<100,A10)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"mgccoop" wrote in message
...
I'm setting up a new report for the month of July and creating formatting
rules for data that will be entered throughout the month. However, when I
apply my rules to cells that have not yet got data entered (eg. for next
week) it is showing up as "formatted" even though the cell is empty. Is
there an override for this conditional formatting so that it the

formatting
won't show up until I enter something in that cell? I'm new to Excel

2007.
Thanks,


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Conditional Formatting on empty cells

Thanks,
When using Excel 2007 I'm stuggling how to enter formulae like that. It was
easier to do that within the old conditional formatting process.

However using what you suggested before I was able to fix it by revising it
from:
Cell Value is less than 70
to
Cell Value is between 1 and 70.

Thanks for your help.


"Ragdyer" wrote:

Add an argument to your formulas that tests for that condition.

For example, if you had a formula:

=A1<100

Revise it to:

=And(A1<100,A1<"")
OR
=And(A1<100,A10)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"mgccoop" wrote in message
...
I'm setting up a new report for the month of July and creating formatting
rules for data that will be entered throughout the month. However, when I
apply my rules to cells that have not yet got data entered (eg. for next
week) it is showing up as "formatted" even though the cell is empty. Is
there an override for this conditional formatting so that it the

formatting
won't show up until I enter something in that cell? I'm new to Excel

2007.
Thanks,



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Conditional Formatting on empty cells

In xl2007, you get lots and lots of rules for conditional formatting.

You could make your first rule:
cell value is =""
and stop if true
And make it the format you like.

Or you could modify the formula so that it makes sure that the cell is nonblank.

Say you want to shade A1 if B1 contains a value 12--but only if A1 has
something in it.

You could use:
formula is:
=AND(B112,A1<"")

===
You may get a better response if you share the formula/rule that you're using.

mgccoop wrote:

I'm setting up a new report for the month of July and creating formatting
rules for data that will be entered throughout the month. However, when I
apply my rules to cells that have not yet got data entered (eg. for next
week) it is showing up as "formatted" even though the cell is empty. Is
there an override for this conditional formatting so that it the formatting
won't show up until I enter something in that cell? I'm new to Excel 2007.
Thanks,


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Conditional Formatting on empty cells

My conditions are
Value 90 = red
value <86-90 = yellow
value < 75-85 = green
value < 70-75 = yellow
Value <70 = red

It is the last one that is causing grief as all blank cells are showing as
red. Your example shows an absolute cell reference but if I type in a range
of cells as below I am getting an error message.
=AND($E$38,$E$6:$E$36<70,<"")

I think I'm close but I'm still missing something.
Thanks,

"Dave Peterson" wrote:

In xl2007, you get lots and lots of rules for conditional formatting.

You could make your first rule:
cell value is =""
and stop if true
And make it the format you like.

Or you could modify the formula so that it makes sure that the cell is nonblank.

Say you want to shade A1 if B1 contains a value 12--but only if A1 has
something in it.

You could use:
formula is:
=AND(B112,A1<"")

===
You may get a better response if you share the formula/rule that you're using.

mgccoop wrote:

I'm setting up a new report for the month of July and creating formatting
rules for data that will be entered throughout the month. However, when I
apply my rules to cells that have not yet got data entered (eg. for next
week) it is showing up as "formatted" even though the cell is empty. Is
there an override for this conditional formatting so that it the formatting
won't show up until I enter something in that cell? I'm new to Excel 2007.
Thanks,


--

Dave Peterson



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Conditional Formatting on empty cells

When using Excel 2007 I'm stuggling how to enter formulae like that. It
was easier to do that within the old conditional formatting process.


Select your cells; click on the "Conditional Formatting" button in the
Styles panel on the Home tab; select "Manage Rules" from the popup menu that
appears; click the "New Rules" button on the dialog box that appears; and
select "Use a formula to determine which cells to format"... the remainder
of the process should look familiar.

Rick

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Conditional Formatting on empty cells

I don't know what you want or why you're specifying E6:E36, but maybe one of
these will help you with the syntax:

=AND($E$38<"",countif($E$6:$E$36,"<70")1)
=AND($E$38<"",max($E$6:$E$36<70))
=AND($E$38<"",min($E$6:$E$36<70))


If these don't help, maybe it's time to describe what you want to check.

mgccoop wrote:

My conditions are
Value 90 = red
value <86-90 = yellow
value < 75-85 = green
value < 70-75 = yellow
Value <70 = red

It is the last one that is causing grief as all blank cells are showing as
red. Your example shows an absolute cell reference but if I type in a range
of cells as below I am getting an error message.
=AND($E$38,$E$6:$E$36<70,<"")

I think I'm close but I'm still missing something.
Thanks,

"Dave Peterson" wrote:

In xl2007, you get lots and lots of rules for conditional formatting.

You could make your first rule:
cell value is =""
and stop if true
And make it the format you like.

Or you could modify the formula so that it makes sure that the cell is nonblank.

Say you want to shade A1 if B1 contains a value 12--but only if A1 has
something in it.

You could use:
formula is:
=AND(B112,A1<"")

===
You may get a better response if you share the formula/rule that you're using.

mgccoop wrote:

I'm setting up a new report for the month of July and creating formatting
rules for data that will be entered throughout the month. However, when I
apply my rules to cells that have not yet got data entered (eg. for next
week) it is showing up as "formatted" even though the cell is empty. Is
there an override for this conditional formatting so that it the formatting
won't show up until I enter something in that cell? I'm new to Excel 2007.
Thanks,


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
How can I deactivate conditional formatting if the cell is empty? Vahe Excel Discussion (Misc queries) 3 January 11th 07 10:11 PM
Excel - Autom. Filter "Empty / Non Empty cells" should come first Rom Excel Discussion (Misc queries) 0 August 10th 05 04:32 PM
How can I convert empty strings to empty cells? Shane Excel Discussion (Misc queries) 2 July 19th 05 12:10 PM
Conditional Formatting Multiple cells based on 2 cells Louis Markowski Excel Worksheet Functions 2 June 1st 05 05:26 PM


All times are GMT +1. The time now is 10:23 PM.

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

About Us

"It's about Microsoft Excel"