Finding largest value in a column

[gpwhite7]

I am looking for a macro that will find the largest value in a column, specifically P6:P266, and change the font color for the largest value only to a different color, say red. And each day when a new entry is made in column P, check to see if the new value is bigger than the previous largest value. Any help would be appreciated.

Thank You

[Richie(UK)]

Hi g,

Is this what you had in mind?

Sub RedRange()
    Dim rngTest As Range, dblTest As Double, rngTarget As Range
    
    With Sheet1
        Set rngTest = .Range("P6:P" & .Cells(Rows.Count, "P").End(xlUp).Row)
        rngTest.Font.ColorIndex = xlAutomatic
        dblTest = Application.WorksheetFunction.Max(rngTest)
        Set rngTarget = rngTest.Find(what:=dblTest)
        rngTarget.Font.ColorIndex = 3
    End With
    
End Sub

HTH

[gpwhite7]

Thanks Richie,

You are the man! It worked exactly the way I wanted it to and I really appreciate your time!

Greg

[PaddyD]

Although a macro might be considered overkill, given the built-in conditional formatting functionality

[Yogi Anand]

Edit: noted after posting that Paddy had already posted the same idea

Hi gpwhite7:

I realize you asked for a macro solution, and Richie has already given you the solution, just wondered why you would not be interested in a non-macro solution as depicted in ...

******** ******************** ************************************************************************>

Microsoft Excel - Book2 ___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
N6 =
	N	O	P	Q	R
6	*	*	3	*	*
7	*	*	7	*	*
8	*	*	36	*	*
9	*	*	6	*	*
10	*	*	11	*	*
11	*	*	22	*	*
12	*	*	36	*	*
13	*	*	22	*	*
14	*	*	5	*	*
Sheet2 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I have used Conditional Formating for Cells P6: P266 with ...

Formula is ... =$P6=max($P$6:$P$266) -- Format|Font > color Red