Change my code to. See below for an easy way to make automatic
Sub HiglightLargestFiveUnique() 'with helper columnn
Cells(1, "l").Value = Application.Max(Range("e2:e72"))
For i = 2 To 5
Cells(i, "l").FormulaArray = _
"=max(if(e2:e72<l" & i - 1 & ",e2:e72))"
Next i

Columns(5).Interior.ColorIndex = 0
On Error Resume Next
ci = 37
For Each cel In Range("l1:l5")
With Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row) 'rng
Set c = .Find(cel, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = ci
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
ci = ci + 1
Next cel
End Sub

To autorun the macro on a change in col E (5)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then HiglightLargestFiveUnique
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
I create a helper column and use that in the findnext
It works but could probably be better.
Assumes data in col E and helper col in col L (could be hidden)

Sub HiglightLargestFiveUnique() 'with helper column
Cells(1, "l").Value = Application.Max(Range("e2:e500"))
For i = 2 To 5
Cells(i, "l").FormulaArray = _
"=max(if(e2:e500<l" & i - 1 & ",e2:e500))"
Next i

Columns(5).Interior.ColorIndex = 0
On Error Resume Next
ci = 33
For Each cel In Range("l1:l5")
With Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
Set c = .Find(cel, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = ci
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
ci = ci + 1
Next cel
End Sub


--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Hi all,

I have a Score column that runs into many entries.

Is there a way to highlight the top 5 scores on the list in different
colours.

Conditional format permits me to highlight the three largest only (yes
another gripe on this much needed excel feature!)

Any help is appreciated in advance!!

Regards
Manosh