Excel – conditional formatting for subsequent rows or columns

conditional formattingmicrosoft excel

I have data in a range of cells (say six columns and one hundred rows). The first four column contains data and the sixth column has a limiting value. For data in every row the limiting value is different. I have one hundred such rows. I am successfully using Conditional formatting (e.g. cells containing data less than limiting value in first five columns are made red) for 1st row. But how to copy this conditional formatting so that it is applicable for entire hundred rows with respective limiting values.

I tried with format painter. But it retains the same source cell (here limiting value) for the purpose of conditional formatting in second and subsequent rows. So, now I am required to use conditional formatting for each row separately s

Best Answer

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

Related Question