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

3 Answers 3


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

  • Didnt work this way
    – sino
    Aug 4, 2017 at 14:15

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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.