Copying conditional formatting formulas

Hi all,

I've actualy already done what I needed to do, but as this is a very sensitive document I would like to know WHY it worked, just to be sure it will remain functional when used over and over...

I have a column of percentages and created three conditions in the conditional formatting rules to show if the percentage adheres to our standards:

If C2 is smaller than 72%, E2 will turn red =C2<72%
If C2 is between 73% and 91%, E2 will turn orange =AND(C2<91%,C2>73%)
If C2 is larger than 91%, E2 will turn green =C2>91%

I've copied this to all other rows below using the Format painter and it works perfectly on every cell.

How ever, if I select a cell lower down (for example E16) and look at the specific conditional formatting for that cell, the formulas remain the same. In the formulas, it still says they're based on the first row (C2). However, the resulting color formatting does match cell C16, as it should.

How and why does this work?

Thanks in advance for your help!

Hi and welcome to the forum

Instead of using the format painter, you can just specify the range in the "applies to" box for each rule.

The reason that each row;s CF seems like it is looking at C2, is because that is where the initial rule is being set - all other rows withing the CF range take their queue from that cell, so all is well, thats how it is supposed to work

Hi Ford,

Thanks for the welcome and quick response!

I see I've made a huge mistake in the orginal post, mixing up rows and columns. Hadn't had my coffee yet I've corrected it, could you please have another look?

If I understand correctly though, the CF takes it's intructions from that first initial rule (based on cell C2). When you copy it down using format painter, it then takes the info from the cell in the column it initialy refered to, but applies it to the specific row it's painted to? Even though the formula still reads it's refering to C2, it actually refers to C16?

I'm so sorry if the question is a bit vague and confusing, English is not my native language

OK so are you saying there is a problem?