Conditional Format Cell Reference Problem - Please Help!!!

steveb1

New Member
Joined
Oct 4, 2006
Messages
2
:biggrin: 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]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Steve

Green:

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

and this for red:

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

Best regards

Richard
 

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
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

New Member
Joined
Oct 4, 2006
Messages
2
THANKS!!

:biggrin: 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.
 

Forum statistics

Threads
1,136,272
Messages
5,674,756
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top