Solved: hightlight highest time

[vzachin]

hi,

i have a column "I" that is formatted hh:mm. the data is separated by an empty row. for each group of data that is separated by an empty row, i need to highlight the highest one icolumn "I" and column "K" in red.

i'm at a loss on how to start this


thanks
zach

[Philcjr]

When you say "highlight" do you want the Font or Cell Fill to be "red"

[Bob Phillips]

...

[Philcjr]

XLD, very slick using the Conditonal Formating... I wrote code to do this... your way is MUCH better.

[Philcjr]

For those that might have wanted to see an approach with coding... here is what I put together


Option Explicit

Sub Highligh_HighestTime()

Dim rRangeTime As Range, rCell As Range
Dim X As Long, Z As Long, LastRow As Long, ElapseRow As Long, MaxTime As Double

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ThisWorkbook.Worksheets("Data")

Let LastRow = .Range("I" & Rows.Count).End(xlUp).Row
Let ElapseRow = .Range("I:I").Find("ELAPSE", LookIn:=xlValues, lookat:=xlWhole).Row + 1

For X = ElapseRow To LastRow

' Loop through to find next empty row
For Z = X To LastRow
If IsEmpty(.Cells(Z, 9)) Then Exit For
Next Z

' With the next empty row, set the range
Set rRangeTime = .Range(.Cells(X, 9), .Cells(Z - 1, 9))

' Find the Max value
Let MaxTime = Application.WorksheetFunction.Max(rRangeTime)

' Loop throug and and apply Red font & bold
For Each rCell In rRangeTime
With rCell
If .Value = MaxTime Then
.Font.Bold = True
.Font.ColorIndex = 3
.Offset(, 2).Font.Bold = True
.Offset(, 2).Font.ColorIndex = 3
End If
End With
Next rCell

' Reset X so it moves to next grouping row
Let X = Z
Next X
End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

[SamT]

This worked in Excel 2003

Option Explicit
Sub Highlight_Highest_Time_in_K()
Dim This_Cell As Range
Set This_Cell = Cells(6, 9) 'The starting point
Dim Cell_to_Highlight As Range
Dim Done As Boolean
Done = False
While Not (Done)
'Just in case you have a one Row Section
Set Cell_to_Highlight = This_Cell
'Otherwise, while the Cell below is not empty, do
While This_Cell.Offset(1, 0) <> ""
'Compare the Time values
If This_Cell.Value > Cell_to_Highlight.Value Then
Set Cell_to_Highlight = This_Cell
' Else: Set Cell_to_Highlight = This_Cell.Offset(1, 0)
End If
Set This_Cell = This_Cell.Offset(1, 0)
This_Cell.Select
Wend
Cell_to_Highlight.Interior.ColorIndex = 3
Cell_to_Highlight.Offset(0, 1).Interior.ColorIndex = 3
'Set things up for the next run:
'At this time This_Cell is empty and just below the section just checked.
'You have two choices:
'If you will never have two empty Rows in your layout;
'First choice
If This_Cell.Offset(2, 0) = "" Then
Done = True
Else
Set This_Cell = This_Cell.Offset(1, 0) 'and repeat
End If
Wend
'I'll leave you to code for IF the above choice is not met.
End Sub

[vzachin]

phil, bob & sam,


thanks for the coding...so many ways


zach