You can select all the cells, and apply the formatting to the active
cell, using relative references.

There's an example he

http://www.contextures.com/xlCondFormat02.html

Jim Johnson wrote:
I have a spreadsheet that tracks several orders (a different order on each
row) and I would like to use conditional formatting to shade different cells
for different conditions. When I apply the formatting to a range of cells,
the formatting is only applied if the condition is met for the entire range.
However, my orders are in different stages for each row. Do I have to apply
the formatting for each cell on each row individually or is there a faster
way to conditionally format several rows at once? Also, can I make the
formatting apply to new entries that are inserted in the middle of other rows
with the same conditions and formats?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html