copying conditional formatting

[David J]

Excel 2010 Win 7 Pro.

I've set up conditional formatting to color numeric cells red if the one above is greater, or green if the one above is less. Works fine on the original cell.

However, if I copy the formatting and then examine the result via Manage Rules, it continues to reference the original cell.

If I then edit the conditional format rule for a cell into which I have copied the formatting to point at the correct cells, when I accept the edit the result is that the formula then points at different cells altogether.

This seems illogical.

What am I doing wrong or misunderstanding?

Thanks for your help.

 

[energman58]

In the CF formula for say D463 you should have D463>D462 (no $ signs)

That evaluates as true if D463 is bigger than D462

The "Apples To" box has $d$463 (with $ signs)

Then if you use format painter or copy paste special then the CF copies across or down fine. You can also edit the rule and edit which cells it applies to as long as there are no $ signs it works on a relative reference. If there are $ signs then you get unexpected results as it turns into an absolute reference.

I think if you use the mouse to build your formula (rather than entering it manually) you get the $ signs by default which may be the root of your problem.

 

[Marcelo Branco]

We've wandered off the subject, I fear.

I queried the way it worked, not how to apply CF.

Ok. Back to your OP.

Suppose that you CF, say A2:B4 (selecting at once the entire range)
=A2>A1 --->Green

After this, if you select B2 and look at CF-rule you see =A2>A1.

Is it wrong or illogical? I dont think so, because if you observe the "Applies to:" you see $A$2:$B$4 that means the CF for any cell in the range A2:B4 is using the formula (relative references or not) of the upper-left-cell.

But if you change the "Applies to:" $B$2, breaking the link with the upper-left-cell the rule changes accordingly to
=B2>B1

IMHO its logical and correct.

M.

 

[David J]

I agree that there is _some_ logic to block references for conditional formatting being handled that way. Sort of consistent with other times when operating on blocks. But I'm not wholly convinced that it is appropriate in this case.

But the behaviour isn't always consistent.

Sometimes it works like this:

Starting with a cell with conditional formatting applied with reference only to itself.

Copy down one row using the little black square at the bottom right of the cell.

Most of the time it works just fine. But now and again the conditional formatting is applied not only to the newly-populated cell, but also the rest of the column from there on down. Even when the extra cells are unpopulated, one extra cell below the newly-populated cell gets coloured in an unwanted way.

On other occasions, after hand editing the comparison cell to point only at the active cell, I then find that the formatting is applied relative to a cell several columns away - so far always to the right.

It was the inconsistency that prompted me to see if anyone else had issues with self-referencing CF. Apparently not.

 

[Marcelo Branco]

I have to say that you have many reasons to feel badly about CF-behavior. I agree.

In fact i was just writing a new post because i observed that changing just the CF of a single-cell, in a range that had received previoulsy a CF, affects the entire range.

Changing the formula and the Applies to of B2, as i did on my previous post, erased the CF of the other cells. It makes no-sense at all!!!

M.

 

[T. Valko]

I have to say that you have many reasons to feel badly about CF-behavior. I agree.

In fact i was just writing a new post because i observed that changing just the CF of a single-cell, in a range that had received previoulsy a CF, affects the entire range.

Changing the formula and the Applies to of B2, as i did on my previous post, erased the CF of the other cells. It makes no-sense at all!!!

M.

Hmmm...

Sounds a lot like this...

Starting in Excel 2007, Microsoft greatly expanded the scope of conditional formatting which is a good thing but at the same time they screwed it up beyond belief by "destroying" the conditional formatting user interface.

 

[Marcelo Branco]

Hmmm...

Sounds a lot like this...

Starting in Excel 2007, Microsoft greatly expanded the scope of conditional formatting which is a good thing but at the same time they screwed it up beyond belief by "destroying" the conditional formatting user interface.

Ok, ok ... i agree ray:



M.