Thread
:
Highlighting the 5 Largest Numbers in a list
View Single Post
#
24
Posted to microsoft.public.excel.misc
Don Guillett
Posts: n/a
Highlighting the 5 Largest Numbers in a list
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
Reply With Quote