Range Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

millwalll - 15 Dec 2007 23:24 GMT

Hi all I have some data that I have sorted then subtotal. Is there way I can
search all cells for the highest number then have the higest number cell
change colour?

Many thanks

I have been trying to think way to do this but not sure what would be best
so want some advice on best option...

Reply to this Message

Gary Keramidas - 15 Dec 2007 23:45 GMT

here's one way, there may be a better way. just change the ranges to your needs.

Sub highlight_largest()
Dim rng As Range
Dim rngfound As Range
Dim ival As Double
Set rng = Range("D14:f16")
ival = WorksheetFunction.Max(rng)
With rng
Set rngfound = .Find(ival, LookIn:=xlValues)
Range(rngfound.Address).Interior.ColorIndex = 3
End With
End Sub

Signature

Gary

> Hi all I have some data that I have sorted then subtotal. Is there way I can
> search all cells for the highest number then have the higest number cell
[quoted text clipped - 4 lines]
> I have been trying to think way to do this but not sure what would be best
> so want some advice on best option...

Reply to this Message

Jim May - 16 Dec 2007 16:58 GMT

A conditional Formatting solution would include (sample range used)

=B1=MAX($B$1:$B$10)

FWIW,

Reply to this Message