conditional formatting for subsequent rows or columns

To add to mischab1's answer: Using Excel 2007, getting to the formula is very simple, but also easy to miss:

1) Click "Manage Rules"

2) Click "Edit Rule"

3) In the rule, you probably have :

  Format only cells with Cell Value =$A$1  

Delete the $ in front of the column # - so that only the column value (A) is absolute:

  Format only cells with Cell Value =$A1 

4) Select OK

5) Now use format painter to apply that rule to the rest of the sheet

If pasting formats doesn't work, you need to take a look at your condition formula. It probably has absolute row references instead of relative row references. You can tell which kind of reference you have by looking for $'s.

So, for example, if your condition formula is =$B$2 > $E$2 then it needs to be changed to =$B2 > $E2. Note that you need to keep the absolute column reference.

For more info about absolute and relative cell references, read this ozgrid page.

If you use the correct formula it will adjust for each row, e.g. if first row is 2 with data in A2:E2 and limiting value in F2 you can select the range A2:E2 and use this formula in conditional formatting

=A2<$F2

Note the single $ in the formula

If you use format painter to copy down the formula will adjust as required (although in Excel 2007 and later versions you won't see that explicitly)

An alternative to using format painter is to select the whole range first, i.e. A2:E100 then apply same formula as above......or in Excel 2007 or later you can simply adjust the "applies to" range within conditional formatting