Copying conditional format formulas down

[spc5000]

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

 

[pgc01]

Hi Simon
Welcome to the board

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

Hope this helps
PGC

 

[spc5000]

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]

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

 

[spc5000]

Thanks again, I will try this on Monday