Excel 2007 - Conditional Formatting copy/paste errors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 11 Mar 2008 13:09 GMT

I find that if I create a conditional format at one cell and then copy that
to a multitude of others, the variable references within the conditional
format will not adjust accordingly. For example:

At cell A1, I have the conditional format of "=IF(A1>0,1,0)". If I copy
this to cell B2:B3 (in one stroke), I will find both cell B2 and B3 to have
the same formula "=IF(B2>0,1,0)". This of course is fine for cell B2, but
it is not fine for cell B3. I would expect cell B3 to reference itself; not
cell B2.

Another problem is that if the receiving cells should have a conditional
format previously in place, the copy from A1 to B2:B3 will append to the
preexisting conditional format as opposed to overwriting them. The only
circumvention I have for this bug is to clear any possible conditional
format before I paste to the receiving cells, B2:B3.

So, how do I resolve this apparent bug in Excel?

btw, this bug is not present in Excel 2003.

Thanks,
Bob.

Reply to this Message

Jim Rech - 11 Mar 2008 15:55 GMT

I don't think either of these behavior changes in Excel 2007 is a bug. MS
defines bugs as things that do not work the way they were designed, and I
think this is what MS designed.

With the first issue, the key thing to be aware of is the "Applies to" part
of the Rules Manager. If the "Formula" is =A1>10" and the "Applies to"
range is B1:B2 then you know that Excel adjusts the rule to =A2>10 when
evaluating in for cell B2. Just the way it adjusts a formula when it is
copied. Had the formula been =$A$1>10 then there would be no adjustment.

Btw, your formula can just be =B2>0 rather than an If. All you need is an
expression that evaluates to a true or false.

Your second issue is definitely a change and not what I would have expected.
But I do think MS intended it. Just something you have to be aware of I
guess.

Signature

Jim

|I find that if I create a conditional format at one cell and then copy that
| to a multitude of others, the variable references within the conditional
[quoted text clipped - 18 lines]
| Thanks,
| Bob.

Reply to this Message

Pete - 11 Apr 2008 23:14 GMT

No, Bob is right. It is a bug. When i use dollar signs to hold the
reference to a single cell, everything works fine. But if i don't use the
dollar signs, everything goes haywire. Some cells in my range get formatted
and some don't, in seemingly random fashion. It's very weird and annoying.

Reply to this Message