find n highlight max value

**only_lonely** · 05-21-2007, 03:50 AM

I have

A
1 2.22
2 2.23
3 2.33
4 1.44
5 3.44

at A6 i putting the formula to find out the max value at A
=MAX(A1:A5)
which is = 3.44

My question is
how to make the system highlited the max value for me
I mean, highlight the cell A5 after find out it is the max value.
So that i know where is the max value located.
Because i have thousands of numbers...which make me headache to find it out

**sweep** · 05-21-2007, 04:14 AM

Hi,

This code will turn the maximum in a selected range green

Sub highlightmax()
Dim rcell As Range
Dim rrange As Range
Dim maxm As Variant
Set rrange = Selection
maxm = Application.WorksheetFunction.max(rrange)

For Each rcell In rrange
If rcell.Value = maxm Then
rcell.Interior.ColorIndex = 4
End If
Next
End Sub

**only_lonely** · 05-21-2007, 04:27 AM

where to insert the code?
sorry..

**sweep** · 05-21-2007, 04:36 AM

No worries,

With your workbook open, press ALT & F11 to open the VB editor

Go to Insert > Module

paste the code into the window that appears.

Close the VB editor.

In your workbook, highlight the range in which you want the highlight to appear, then go to Tools>Macro>Run Macro, and select highlightmax

Dave