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.

 

[T. Valko]

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.

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.

I don't have Excel 2010 but I'm guessing it's just about the same as Excel 2007 WRT CF.

The trick to setting the CF is to apply it all at the same time. Typically, you can select the entire range of cells you want to format and apply the CF in one step avoiding having to replicate it on other cells in separate steps.

If you can tell us what range of cells you want formatted it would help us help you.

 

[Marcelo Branco]

Hi,

Assuming your CF formulas for cell A2 are
=A2>A1 ----> Green
and
=A2<A1 ----> < A1 ---> Red

You can use the Format Painter (yellow brush) to copy these CF conditions to any cell (or to a range of cells).

HTH

M.

 

[David J]

I don't think you have addressed my question.

It's not the copying that is the problem, it is the result of copying.

 

[Marcelo Branco]

I don't think you have addressed my question.

It's not the copying that is the problem, it is the result of copying.

David,

If you copy the formulas in A2 (my example of previous post) using the Format Painter to, say A4, the formulas become
= A4 > A3 ---> Grenn
= A4 < A3 ---> Red

and the results change accordingly

Is it not ok?

M.

 

[David J]

Using either copy and paste formats or the format painter over several cells gives a linked format which I don't want.

Using the format painter with CTL-click to hold the source, and pasting the format into one cell at a time gives the result I want. But that doesn't match my workflow.

The question arises because I copy a row down and overwrite with the next set of figures using the original formatting. The conditional format viewed via Manage Rules shows the condition as originating in the source cells, not the copied ones. Although the format works OK, the references are not appropriate.

 

[Marcelo Branco]

Using either copy and paste formats or the format painter over several cells gives a linked format which I don't want.

Using the format painter with CTL-click to hold the source, and pasting the format into one cell at a time gives the result I want. But that doesn't match my workflow.

The question arises because I copy a row down and overwrite with the next set of figures using the original formatting. The conditional format viewed via Manage Rules shows the condition as originating in the source cells, not the copied ones. Although the format works OK, the references are not appropriate.

David,

I would like to know your formula to build the original CF. Could you post it?

M.

 

[David J]

The original formula is, as you have already said, for example:

In cell D463

D462>D463 applies to $D$463

I cannot avoid the fixed reference to $D$463 - Excel puts in the dollar signs even if I miss them out by manual editing.

When this is copied down to D464, it ends up as:
D462>D463 applies to $D$463:$D$464

D462<D463 p to applies $D$463:$D$464<>
I contend that it should read:
D462>D463 applies to $D$464

On occasions (although not this one), when I have edited the formula to refer to the formatted cell and then accepted my edit, on returning to edit again the cell reference formula has been displaced by several cells from that which I entered manually. This, too, seems anomalous.

I hope this clarifies the issue.

(BTW, the board doesn't seem to like lessthan signs and omitted the rest of the row when I tried put them in to the complementary formulas for the opposite test)

 

[Marcelo Branco]

I'm not sure if this helps, but you can change manually in the original formula

Applies to
$D$462:$D$468

for example, and the CF will apply to the entire range above.

M.

 

[David J]

We've wandered off the subject, I fear.

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