Conditional formatting

[infuseinfuse]

I have these two formulas in the conditional formatting in cell B3 =AND(B3<20,$K$1="RM").....
=AND(B3>20,$K$1="RM").....
The problem is the reference is not changing when i use the format painter and copy the formula across the columns. I want the reference to change when i copy it across. like =AND(C3<20,$K$1="RM").....=AND(D3<20,$K$1="RM").....

I am using Excel 2007

 

[Andrew Poulsom]

Welcome to MrExcel.

If you have those formulas they should adjust relatively when copied. If they don't make sure that you don't have any $ signs in the reference to B3.

 

[infuseinfuse]

It is not doing for me.. Is it working for anyone here? Can anyone check this?

Welcome to MrExcel.

If you have those formulas they should adjust relatively when copied. If they don't make sure that you don't have any $ signs in the reference to B3.

 

[Andrew Poulsom]

I think you will find that it is working, although the formula may not be what you expect when you examine it through Manage Rules. The formula that appears there is the one for the top left cell in the Applies to range. It's explained here:

http://blogs.office.com/b/microsoft.../conditional-formatting-rules-simplified.aspx

I agree that it's not particularly intuitive.

 

[infuseinfuse]

Sorry for the delay...Thank you Andrew. I read the link you quoted and understood how it works. My problem is solved now... Thank you once again

I think you will find that it is working, although the formula may not be what you expect when you examine it through Manage Rules. The formula that appears there is the one for the top left cell in the Applies to range. It's explained here:

http://blogs.office.com/b/microsoft.../conditional-formatting-rules-simplified.aspx

I agree that it's not particularly intuitive.