Thanks - very helpful!

"Bernard Liengme" wrote:

1) I typed =RANDBETWEEN(1,100) in a10 and copied it down to A20
2) With A1:A20 selected, I use Conditional Format,; specified Formula Is
=RANK(A1,$A$1:$A$20)=1 and set colour red
3) I added =RANK(A1,$A$1:$A$20)=2 and set colour blue
4) I added =RANK(A1,$A$1:$A$20)=3 and set colour green

When I hit F9 to recalculate the formulas the colours move about as
required.
But you want 5 criteria and the maximum is 3 (until you get XL2007)
So you will need a macro, see
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sarah" wrote in message
...
Hi there,

I have a column of data that changes regularly. I want to set up
conditional
formatting whereby the top 5 datapoints show up in a different color to
the
others. I think I have to use Rank for this but am not sure how?
Thanks!