Copying conditional format formulas down

spc5000

New Member
Joined
Oct 6, 2006
Messages
3
H

I have a row with 5 numbers in it and I want the highest number to be displayed in blue.

I am using conditional formating as follows:

Cell value is ---- greater than or equal to---- for each of these cells.

Cell 1 =MAX(AJ:AN)
Cell 2 =MAX($AI$3,$AK$3,$AL$3,$AM$3,$AN$3)
Cell 3 =MAX($AI$3,$AJ$3,$AL$3,$AM$3,$AN$3)
Cell 4 =MAX($AI$3,$AJ$3,$AK$3,$AM$3,$AN$3)
Cell 5 =MAX($AI$3,$AJ$3,$AK$3,$AL$3,$AN$3)
Cell 6 =MAX($AI$3:$AM$3)

It all works fine, but when I copy down, the formulas stay relative to row 3 (ie $AI$3)

I have tried copying the normal way and have tried the format painter.

Does anyone know the solution or another way to do this as I have about 500 lines to copy down and I dont fancy doing it all by hand!

Cheers

Simon
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Simon
Welcome to the board

The $ sign before the 3 freezes the row. Delete it.

Hope this helps
PGC
 

spc5000

New Member
Joined
Oct 6, 2006
Messages
3
That worked great - thanks very much !!!!

One final point:

When all the values are on 0 (zero) how do I stop them from displaying the blue formating? Greater values are OK to show the formatting....

Thanks again.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again

Use the conditions you had as second condtions. In the first condition check if they are all 0 and set format to nothing.

In the first condition choose Formula is and use something like

=and(A1=0,B1=0)

Hope this helps
PGC
 

Forum statistics

Threads
1,141,677
Messages
5,707,777
Members
421,527
Latest member
Tamiwsw

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