#### steveb1

##### New Member
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.

Steve.[/b]

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Andrew Poulsom

##### MrExcel MVP
Can you post your CF formulas please?

#### Richard Schollar

##### MrExcel MVP
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
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
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.

Replies
6
Views
948
Replies
0
Views
340
Replies
4
Views
518
Replies
8
Views
598
Replies
3
Views
377

1,171,588
Messages
5,876,320
Members
433,193
Latest member

### 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?

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