Highlighting the 5 Largest Numbers in a list

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

Which code?

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
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

Manosh,

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

--

Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533133

Did you fully test this?

--
Don Guillett
SalesAid Software

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.26cnum_1145178601.7032@excelforu m-nospam.com...

A slight mod to the code supplied by L. Howard Kittle to remove errors,
and to allow for the last (few) figures being deleted.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
Dim lr As Integer
Dim rng As Range
Dim Cell As Range

lr = Cells(Rows.Count, "e").End(xlUp).Row
Set rng = Range("e2:e" & lr + 200)
rng.Interior.ColorIndex = 0 ' clear beyond last

Set rng = Range("e2:e" & lr)

For Each Cell In rng
On Error Resume Next

If Cell.Value = Application.Large(rng, 1) Then

Cell.Interior.ColorIndex = 5 ' dark blue
Else

If Cell.Value = Application.Large(rng, 2) Then
Cell.Interior.ColorIndex = 4 ' green
Else

If Cell.Value = Application.Large(rng, 3) Then
Cell.Interior.ColorIndex = 6 ' yellow
Else

If Cell.Value = Application.Large(rng, 4) Then
Cell.Interior.ColorIndex = 7 'viloet
Else

If Cell.Value = Application.Large(rng, 5) Then
Cell.Interior.ColorIndex = 8 'light blue
Else
End If
End If
End If
End If
End If

NextCell:
On Error GoTo 0

Next

End Sub


Hope this helps

--


Wrote:
This is turning out to be an interesting exercise :-)

Now there is a Run Time Error '13 Type Mismatch when there are no
numbers and i start to enter them one by one.

For example, if the code is already in the sheet, and the numbers
start
to be put in the cell the error box pops up when Enter is pressed on
the first cell with a new number (all the other cells in the range are
currently blank).

Ideally this error should not appear... and yet the code should be
'live'. This would be most useful for novice user like us!

I am running out of thank yous!

regards
manosh


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533133

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:e72l" & 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:e500l" & 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