Prev: Combine two columns and delete original
Next: Change Field On Pivot Table Using VBA Input Box
From: mgccoop on 4 Jul 2008 13:49
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,
From: Ragdyer on 4 Jul 2008 14:05
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,A1>0)
--
HTH,

RD

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

"mgccoop" <mgccoop(a)discussions.microsoft.com> wrote in message
news:2BBD8371-67FA-4A83-99D0-239C1BB9F459(a)microsoft.com...
> 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,

From: mgccoop on 4 Jul 2008 14:23
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,A1>0)
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "mgccoop" <mgccoop(a)discussions.microsoft.com> wrote in message
> news:2BBD8371-67FA-4A83-99D0-239C1BB9F459(a)microsoft.com...
> > 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,
>
>
From: Dave Peterson on 4 Jul 2008 14:24
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(B1>12,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
From: mgccoop on 4 Jul 2008 14:52
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(B1>12,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
>
 |  Next  |  Last
Pages: 1 2
Prev: Combine two columns and delete original
Next: Change Field On Pivot Table Using VBA Input Box