Conditional formatting works, but also on empty cells! Fix, or is there a better way?

[StargateFanNotAtHome]

Condition 3 in conditional formatting for cell G3 makes the cell
background colour and font attributes change when a dollar amount
doesn't change between rows. When we're missing an invoice, I type in
the invoice date, then under invoice amount put "0". The conditional
formatting for the total cell changes the light colour to a darker hue
and bolds and darkens the font. This points out to everyone that the
invoice hasn't been received yet, or whatever.

So in G3, for example, the 3rd conditonal formatting details are:
Cell value is equal to =$G2

But the same is happening in unused cells. Of course, although there
is no data, the condition of G14 being the same as G13 is met because
they're both empty, which is no good. The cell background should
change only where there is no change iun the dollar value not when
there is no change since they're both empty <g>.

Perhaps there is a better way to do the same, so that the conditional
formatting does it job yet leaves empty, unused rows alone?

Thanks! D

 

[Bernie Deitrick]

SFNAH,

Select your cells, with G3 as the active cell, then apply CF but use the
Formula is... option with the formula

=AND(G3=G2,G3<>0)

You can make those formulas as complex as your need, as long as they return
TRUE or FALSE....

HTH,
Bernie
MS Excel MVP

 

[Bob Umlas, Excel MVP]

Change to "Formula Is", enter:
=AND($G3<>"",$G3=$G2)

 

[StargateFanNotAtHome]

SFNAH,

Select your cells, with G3 as the active cell, then apply CF but use the
Formula is... option with the formula

=AND(G3=G2,G3<>0)

You can make those formulas as complex as your need, as long as they return
TRUE or FALSE....

Hi, Bernie! Thanks.

I'm obviously doing something wrong because the same thing problem is
occurring. I selected everything and did it as you suggested and
applied CondForm but, wham, empty cells changed again.

I took off CondForm then selected cell G4 (sorry it was G4 not G3 but
same thing applies), and then did this in "Formula is" in CondForm
condition #3:
=AND($G4=$G3,$G4<>0)
then repeated down and same thing. Empty cells get coloured.

Perhaps what you said about TRUE or FALSE is what is wrong? I don't
know what that means so maybe answer lies there (?).

 

[StargateFanNotAtHome]

Hi, Bernie! Thanks.

I'm obviously doing something wrong because the same thing problem is
occurring. I selected everything and did it as you suggested and
applied CondForm but, wham, empty cells changed again.

[snip]

I ended up doing what I know. Don't know if this is the right way to
do this but it _seems_ to be doing what I need. When there is no
change, the cell background darkens, font is bolded and is italicized.
Yet the empty rows aren't affected.

The formula I put in this particualr sheet, which happens to E3 in
this one:
=IF($E3<>"",$E3=$E2,"")
seems to work.

Anyway, thanks. D