Conditional Format Cell Reference Problem - Please Help!!!

[steveb1]

Hi Everyone,

I am using Excel 2003 and trying to do the following.

I have a column of data that looks like this:

A1 99.70
A2 100
A3 89.5
A4 93.2

I have applied a conditional format to A1 and A2 to turn them both green if A2=100 else red if A2 is less than 100.

I then want to copy the conditional formatting to A3/A4 but in doing so I want excel to transpose the cell references so the conditions no longer check if A2 is <=100 but instead check A4. This is how excel behaves with formulas when copying and pasting but does not seem to do the same with copying and pasting conditional formatting.

I have tried various paste special options with no success.

Thanks in advance
Steve.[/b]

 

[Andrew Poulsom]

Can you post your CF formulas please?

 

[Richard Schollar]

Hi Steve

Green:

=OFFSET(A8,MOD(ROW(),2),0)=100

and this for red:

=OFFSET(A8,MOD(ROW(),2),0)<100

Best regards

Richard

 

[irresistible007]

I amn't sure but then as i understand you would have applied the folmula like in this way:

You selected A1 and A2 clicked conditional Formatting and then entered the formula exactly like: FormulaIS =A2=100

If so... you are mistaking here cuz in this way, for Cell A1 excel will check whether if the value of Cell A2 is equal to 100 (in this case true) and will turn it to green... But.... For A2 (since both are req to be green if the condition is met)... excel will simply run the condition A3=100

Prob here is that you even can't use $A$2.... cuz then the excel wont run the condition A4=100 ... which is actually needed by you..

Hope clear

 

[steveb1]

THANKS!!

Richard thanks for the help and the prompt response, I spent ages trying to figure this out. I now have it working. Also thanks to the you guys who responded. I will use this forum again, I am extremely impressed.