Range Question

[millwalll]

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...

 

[Gary Keramidas]

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

 

[Jim May]

A conditional Formatting solution would include (sample range used)

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

FWIW,