how can I copy a conditional formatting in Excel 2010 to other cells, which is based on a other cells content?

Question

I need to copy a formula based conditional formatting to other cells but i have to change the formula for every single cell condition. how can I do a copy of this condition so that the formula changes accordingly as well? in cells, when copy cells with formula referring to other cells, then excel will change the referring cells accordingly. But I don't know how to do the same in conditional formatting so that I don't need to change my conditional formula manually:

condition: =K21+$F22 (the conditional formula which I put in cell K22) condition: =AB21+$F22 (the conditional formula which I need to be in cell AB22)

As I have more than 10000 cells to apply my conditional formatting formula, it's almost impossible to type this formula manually in every single cell. Is there a way to do it automatically?

I don't know VB but I can learn if the solution is there only. But please give me no abbreviation and explain me completely.

Thanks a lot! Shahab

Answer 1

I ran into the same situation where when I copied the formula to another cell the formula was still referencing the cell used in the first formula. To correct this when you set up the rules, select the option "use a formula to determine which cells to format. Then type in the box your formula, for example H23*.25. When you copy the cells down the formulas will change to H24*.25, H25*.25 and so on. Hope this helps.