Excel 2007 - Conditional Formatting copy/paste errors

B

Bob

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.
 
J

Jim Rech

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.


--
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
| 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.
|
 
P

Pete

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top