View Single Post
  #24   Report Post  
Posted to microsoft.public.excel.misc
Don Guillett
 
Posts: n/a
Default 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