Conditional formatting with Rank
 

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!

Conditional formatting with Rank
 

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!

Conditional formatting with Rank
 

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!

Conditional formatting with Rank
 

No answers yet,,,, so I assume that it is rather difficult or impossible
with conditional formatting.

The other way to do it is with a macro.

This macro will insert a column next to the data column, and fill the new
temporary column (B) with a range
from 1 to 1000.
then it will sort the original data (here in column A)
highlight the top five cells
re-sort the data by the new column B to get the A column back
into the normal unsorted order and
then it will delete the temporary column B.

If you have problems with the use of the macro send me an email direct.

Sub Macro3()

Range("B1").Select
Selection.EntireColumn.Insert
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B1000"), Type:=xlFillSeries
Range("B1:B1000").Select
Range("A1:B1000").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:A5").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:B1000").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B1").Select
Selection.EntireColumn.Delete
Range("A1").Select
End Sub
--
Greetings from New Zealand

"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!