Copy Conditional Formatting down rows

[Whit]

New to Office 2007, but am very frustrated. When I copy a cell's formatting
(either by paste/special/formats or format painter) the conditional
formatting is not using the relative cell row reference but instead always
referring back to the original cell even though I am not using the absolute $
reference. For example:

CF = A10>100 (in cell A10)

when I copy the format to B10 CF = A10>100
If I copy the format to B10:F10 CF in all cells = B10>100

What is going on??

 

[T. Valko]

It works ok for me. Here are the steps I used:

Select the range A10:F10
Goto Home tab>Styles>Conditional Formatting>Manage Rules>New Rule>Use a
formula to determine which cells to format

=A10>100

Click the Format button
Select the desired style(s)
OK out

 

[Whit]

I'm trying to use the Format Painter or Copy Special/Format options to copy
CF like I have been able to do in prior versions. In my spreadsheet, it is
not progressing the rows like it should in a CF formula like
=AND(C12>0,D12<>1) and acting like I have entered it as =AND(C$12>0,D$12<>1).
It worked for a while under some special conditions, but now no matter what
I do it replicates the exact same formula without progressing the rows like
it should. Is there some kind of option that I need to set, is this a bug,
???

 

[T. Valko]

Using Copy then Paste Special>Formats still works ok for me.

Tell me *exactly* what cells you're trying to format based on what
condition.

I've *never* used the format painter so I don't know how it works.

 

[Whit]

I finally figured it out by trial and error. In Excel 2003, when you look at
the conditional formatting it always shows the relative position like on cell
C4 the calc for a CF would show as =C4>0. In Excel 2007 if I copy CF using
somthing like Format Painter down a column like from C3:C6, when you look at
the CF for cell C5 it shows as =C3>0 (the "anchor apply to row range"). This
was confusing me because it looked like it was still referencing the C3 value
but after trying it out it is actually referencing the correct C5 value. Why
MS made this change is beyond me.

Thanks for responding

 

[T. Valko]

Ok, glad you got it straightened out.

Why MS made this change is beyond me

I'm in total agreement on that. The cf user interface in Excel 2007 is not
very user friendly!