Conditional formatting - quick way to apply to multiple cells?

Hi,

Is there a quick way to apply the same general rule to multiple cells (such as in a table) without having to manually input the condition each time?

Say I have the rule that when the cell B1 is less than A1 it is highlighted red. I want to apply this to the subsequent rows of the table, B2 < A2, B3 <A3 etc. When I click the bottom right hand corner of the first cell (B1) and then drag it down it copies the conditional formtting rule but it references A1 every time. How I can quickly make it reference the adjacent cell? So far I have manually changed each row.

Thanks for any info.

Make sure that your rule doesn't make row 1 in A1 absolute. I.e there are not $ in front of the 1.

Also, you just need to change the applies to range when Managing the Rule. Change it to the range it applies to.

The actual conditional formula should be for the top-left most cell only, then you add a range to the applies to field, and it will automatically adjust the cell references relatively.

So if you want to highlight B1 use Rule Cell Value Is >> Less than >> =A1

or use formula =B1<A1

and apply to B1:Bx

If you still have trouble getting it.. upload a sample workbook.