copy conditional formats

[excelWalter]

I've spent the past 2 days researching this issue, but have come up dry.
I'm hoping you can help me.

I have a range of cell, 2 abreast in this instance, and I have placed
conditional formatting on them.

1) if B2 = x than range (B22) is green
2) if B2 = y than range (B22) is yellow
3) if B2 = z than range (B22) is red

...

1) if B6 = x than range (G6:I6) is green
2) if B6 = y than range (G6:I6) is yellow
3) if B6 = z than range (G6:I6) is red


This works great.

My quandary is "copying" this conditional formatting to other cells.

( Column B->D Rows 2->26 ) and ( G->I Rows 18->40 ) need the same
formatting, but each row is based on the value of it first column of
the set (B & G) of that row.

I've used PAINTER. I've used PASTE->Special.

All these options copy the formatting alright, but it does not change
the referential cell. It all points back to B2.

I have a pretty large sheet I would like to format this way, and it
would take hours to hand re-format each range.

Not to mention when I add more to this set, and I will.

Do you have any tricks you can share.

Thanks again for your help.

Walter

 

[Dave Peterson]

You could do it in two steps:

Select B226
(keep B2 the activecell)

Write your conditional formatting formula using B2, but use $b2 as the
reference. Each of the other rows will get the same formula (offset to match
its row), but the $B will mean that it's always looking at the value in column B
of that row.

Then do the similar thing for G18:I40 (Use $g18 as the reference.)

 

[dnickelson]

also, when you are trying to copy and paste, the above tips touches on
the reason it always refers to B2. Conditional formats default to
putting the $ in front of both the column and row. Because of this,
when you paste the format, it points literally to the cell B2.

If you just get rid of the $ in front of the 2, leaving $B2, as
mentioned above, your conditional format should adjust correctly in the
future (when you start adding additional fields and may not want to
redo the formula for the entire range.)
Hope this helps.