Office2000: Conditional format behaves strangely

Hi

On a worksheet with premade formulas I used conditional format to mark out
cells with formulas for rows where key kolumn cell (week number - the sring
in format "yyyy.ww") is not empty. The condition p.e. for cell AA126 is set
to
Formula Is =($B1026<>"")
and when TRUE, then the cell is filled pale blue with dashed borders
(default format is no fill, no borders, color automatic), when not, the cell
looks unformatted like any empty cell. It works excellent.

There was a need for additional conditional format check: the sum of 2
columns values must always equal with value in 3rd column - otherwise there
is some wrong entry on row (p.e. some value of wrong type was copied into
some cell). P.e. for cell AB126 is the conditional format now:
1)
Formula Is =(SUM(AC1026:AD1026)<>AB1026)
when TRUE, the fill is pale blue, borders are dashed, and font is dark red;
2)
Formula Is =($B1026<>"")
when TRUE, the fill is pale blue and borders are dashed

This format works, when there is a week number in cell B1026 - when
SUM(AC1026:AD1026)=AB1026, then cell is colored and font is black
(automatic), when the sum doesn't equal with test value, the font is red in
colored cell. But when the cell B1026 is empty, the cell is formatted like
the second condition returned TRUE - it's colored and has dashed border!

For testing I copied the formula from second condition into free cell on
worksheet - the formula returns FALSE. Adding 3rd condition:
Formula Is =($B1026="")
with formats no fill, no borders, automatic color
didn't help either. Has someone any ideas about this?

Thanks in advance!

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets

Hi


Forget it! The cause was 1st condition, which was false for cases when
formula in column AB returned "". I simply changed the first condition to:
Formula Is =(SUM(AC1026:AD1026)<>SUM(AB1026))

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Arvi Laanemets" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> On a worksheet with premade formulas I used conditional format to mark out
> cells with formulas for rows where key kolumn cell (week number - the
sring
> in format "yyyy.ww") is not empty. The condition p.e. for cell AA126 is
set
> to
> Formula Is =($B1026<>"")
> and when TRUE, then the cell is filled pale blue with dashed borders
> (default format is no fill, no borders, color automatic), when not, the
cell
> looks unformatted like any empty cell. It works excellent.
>
> There was a need for additional conditional format check: the sum of 2
> columns values must always equal with value in 3rd column - otherwise
there
> is some wrong entry on row (p.e. some value of wrong type was copied into
> some cell). P.e. for cell AB126 is the conditional format now:
> 1)
> Formula Is =(SUM(AC1026:AD1026)<>AB1026)
> when TRUE, the fill is pale blue, borders are dashed, and font is dark
red;
> 2)
> Formula Is =($B1026<>"")
> when TRUE, the fill is pale blue and borders are dashed
>
> This format works, when there is a week number in cell B1026 - when
> SUM(AC1026:AD1026)=AB1026, then cell is colored and font is black
> (automatic), when the sum doesn't equal with test value, the font is red
in
> colored cell. But when the cell B1026 is empty, the cell is formatted like
> the second condition returned TRUE - it's colored and has dashed border!
>
> For testing I copied the formula from second condition into free cell on
> worksheet - the formula returns FALSE. Adding 3rd condition:
> Formula Is =($B1026="")
> with formats no fill, no borders, automatic color
> didn't help either. Has someone any ideas about this?
>
> Thanks in advance!
>
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>
>