Highlighting the 5 Largest Numbers in a list

[manoshde]

All!

My sincere thanks to you who contributed to this exercise!

I am using the code and it seems robust and works well for my purpose!

Cheers!
Manosh

 

[Don Guillett]

Which code?

 

[Bryan Hessey]

Manosh,

It is good to see that the code works for you, and thank you for your
response

--

 

[Don Guillett]

Did you fully test this?

--
Don Guillett
SalesAid Software
(e-mail address removed)
"Bryan Hessey" <[email protected]>
wrote in message

 

[Don Guillett]

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