Copy/Past Conditional Format

[Ken]

Good morning board ...

Presently, I have approx 15 rows by 100 columns loaded
with Conditional formatting ... working ok so far.

Now I need to extend this same pattern down ... maybe
skipping a couple of rows between each pattern.

Issue ... Conditional format formulas are not changing
when I do copy/paste due to $ locking the cell positions.
And if I take the $ out ... then Conditional format
appears to fail where they already exist.

I am certain this can't be unique, but I do not know the
fix or work around ... Therefore, I am coming to the many
Magicians of this board ... Thanks ... Kha

 

[Marcel]

Have you tried the format painter on the Standard tool bar
the little paint brush " highlight one of your row...with
the require format..then single click on the paint brush
for transfering it one or double click on it to transfer
it to mutiple cell...when your done just press Esc that
will deactivate your format painter...

 

[Paul]

Good morning board ...

Presently, I have approx 15 rows by 100 columns loaded
with Conditional formatting ... working ok so far.

Now I need to extend this same pattern down ... maybe
skipping a couple of rows between each pattern.

Issue ... Conditional format formulas are not changing
when I do copy/paste due to $ locking the cell positions.
And if I take the $ out ... then Conditional format
appears to fail where they already exist.

I am certain this can't be unique, but I do not know the
fix or work around ... Therefore, I am coming to the many
Magicians of this board ... Thanks ... Kha

Conditional format formulas work the same as ordinary worksheet formulas as
regards absolute/relative/mixed referencing and copy/paste. The bit to be
careful about is what this means when you select more than one cell and then
type in a conditional format formula.

As a simple example, suppose you select A1 and B1 (with a1 as the active
cell) and type in the CF formula
=(A1=$A$1)
Precisely this formula is what determines the format of A1. But the formula
adjusts to
=(B1=$A$1)
for cell B1 (just as a normal formula would if copied/pasted within the
worksheet).
You can see this by selecting A1 and B1 in turn, each time going back into
conditional formatting as though to edit the CF.

If you then copy row1 formatting to row 2, the formatting of all four cells
A1:B2 will depend on $A$1. This may be what you want. However, you may have
wanted the formatting of each row to depend on its own column A cell. In
this case, you should have used the CF formula
=(A1=$A1)
(note the mixed reference). This will adjust correctly for B1. However, when
copied to row 2, only the column reference will be fixed and the CF for B2
(for example) will be
=(B2=$A2)

Just like ordinary formulas within the worksheet, it all depends on what you
want to achieve.