Excel 2003 bug

[Shawn Casey]

This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition becomes
true.

This isn't the same behavior as when cell values are used. If this is the
intended behavior, there should at least be an option to continue through
all conditions.

 

[Ron Rosenfeld]

This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition becomes
true.

This isn't the same behavior as when cell values are used. If this is the
intended behavior, there should at least be an option to continue through
all conditions.

I've never seen the behavior you claim to occur when using cell values. Could
you please post a specific example?

CF has always worked for me as documented, whether I use cell values or
formulas.


--ron

 

[Earl Kiosterud]

Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect you've set
up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border, and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own condition, I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net

 

[Shawn Casey]

Thanks for looking at this Earl, you are right, this is what i'm trying to
do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

 

[Earl Kiosterud]

Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Thanks for looking at this Earl, you are right, this is what i'm trying to
do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect you've set
up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border, and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own condition, I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net

 

[Shawn Casey]

That was a simple example of how their is a bug in Excel, but as shown in
the second bug example, the real scenario involves rows below the current
row. When I insert rows, the cell references become messed up using
relative addressing.

Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Thanks for looking at this Earl, you are right, this is what i'm trying to
do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect you've set
up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border, and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own condition, I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition becomes
true.

This isn't the same behavior as when cell values are used. If this

is
the

intended behavior, there should at least be an option to continue through
all conditions.

 

[Earl Kiosterud]

Shawn,

Yeah. The RC reference style adjusts in the same way that the A1 style does
when you insert rows, or do anything that moves stuff.

You can refer to another row with the following, which seems to work in an
AND in conditional formatting. The following is for the cell immediately
below the active one. If rows are inserted, it continues to refer to the
cell below the active one.

=AND(OFFSET(B2,1,0,1,1)<>"", TRUE)

Again, adjust B2 to match the active (white) cell in your selection. The
formula is actually referring to itself, but doesn't activate a circular
reference raspberry, since there's an offset.

Is this what you need?
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

That was a simple example of how their is a bug in Excel, but as shown in
the second bug example, the real scenario involves rows below the current
row. When I insert rows, the cell references become messed up using
relative addressing.

Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net

trying

to

do. It sure would be nice if it was an option on the conditional formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer

fails
to

work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are fine.
Here are the column conditional formatting rules, all have 3 formulas with
the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

"Earl Kiosterud" <mvpearl omitthisword at verizon period net> wrote in
message Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive (like
red for one condition, green for another, etc.). But I suspect

you've
set

up formats that are not exclusive (like red for one condition, bold for
another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one of the
three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where

more
than

one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font,

border,
and

patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own

condition,

I

 

[Shawn Casey]

That is exactly what I needed (I used RC instead of B2 of course since I
applied it over the entire column).
Thanks for the work around to the bugs!

The weird thing about bug #2 is that the references were only messed up for
one of the columns...

Shawn,

Yeah. The RC reference style adjusts in the same way that the A1 style does
when you insert rows, or do anything that moves stuff.

You can refer to another row with the following, which seems to work in an
AND in conditional formatting. The following is for the cell immediately
below the active one. If rows are inserted, it continues to refer to the
cell below the active one.

=AND(OFFSET(B2,1,0,1,1)<>"", TRUE)

Again, adjust B2 to match the active (white) cell in your selection. The
formula is actually referring to itself, but doesn't activate a circular
reference raspberry, since there's an offset.

Is this what you need?
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

That was a simple example of how their is a bug in Excel, but as shown in
the second bug example, the real scenario involves rows below the current
row. When I insert rows, the cell references become messed up using
relative addressing.

Shawn,

It appears you want to apply the color if the cell to the left is not empty.
The INDIRECT seems not to work inside an AND as you've said. Try this
instead:

=AND( B2<>"", TRUE)

Change the B2 to the address of the cell that's left of the active (white)
cell in your selection. It will be changed relatively (as with a copied
formula) for all cells in the selection, thus always examining the cell
that's one left of the one being conditionally formatted.

Earl Kiosterud
mvpearl omitthisword at verizon period net

trying

to
do. It sure would be nice if it was an option on the conditional
formatting
to not stop on first true, but continue down the list applying what
satisfies the condition.

Now there seems to be two other bugs that I've found.


1. Using indirect addressing within the AND statement for conditional
formatting doesn't work.

example:
if the conditional forrmating is:
formula is: =(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"")
this evaluates to true but if the conditional formatting is:
formula is: =AND((INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>""), TRUE )
this doesn't evaluate to true.

Of course, i'm using something more than TRUE, but this reproducer fails
to
work properly.

I got around this problem by using r1c1 style references and the formula:
formula is: =AND(rc[-1]<>"", TRUE)
evaluates to true correctly.

Using this caused me to find the next bug:

2. Inserting a row modifies unpredictably modifies the conditional
formatting.

I applied conditional formatting over three columns that uses r1c1 style
references as shown above. When I insert a row, the first column's
conditional formatting isn't global to the column any more because the
references around which i've inserted have been linked to the old cells,
causing them to be unique. What's weird is the other two columns are
fine.
Here are the column conditional formatting rules, all have 3

formulas
with

the first formatting the cell according to condition 2 and 3.

Column 1:
=AND(RC[-1]<>"", R[1]C<>RC)
=R[1]C<>RC
=RC<>""

Column 2:
=AND(R[1]C[-1]<>RC[-1],RC<>"")
=R[1]C[-1]<>RC[-1]
=(RC<>"")

Column 3:
=AND(RC[-1]<>"", R[1]C[-2]<>RC[-2])
= R[1]C[-2]<>RC[-2]
=RC[-1]<>""



It seems like the deeper I dig, the more holes I find.

Thanks,
Shawn

"Earl Kiosterud" <mvpearl omitthisword at verizon period net> wrote in
message Shawn,

Conditional formatting will apply formats for only one of the three
conditions. It has no choice if you have formats that are exclusive
(like
red for one condition, green for another, etc.). But I suspect you've
set
up formats that are not exclusive (like red for one condition,

bold
for

another) where you'd like to have them both applied when both their
conditions are TRUE. But since it applies format(s) for only one

of
the

three conditions, it stops after finding the first of the three formulas
that resolves to TRUE (thus prioritizing them for the case where more
than
one condition is satisfied -- it uses the first condition that is TRUE).
Remember that formats from any or all of the three tabs (font, border,
and
patterns) can be applied for each condition.

If you want to apply multiple formats, each with their own

condition,

I
suspect you'll have to do that with a macro.

Or this the condition where I've TOTALLY missed the point?

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

This is a bug in all versions of Excel.

When using conditional formatting with more than more formula, the
formatting ceases to go through all conditions once one condition
becomes
true.

This isn't the same behavior as when cell values are used. If

this
is

the
intended behavior, there should at least be an option to continue
through
all conditions.