application.min() will result in the smallest number (including date).

For example, say 12/25/2000 was the smallest date.

Range(12/25/2000)
doesn't make much sense.

You could try using application.match() or even .cells.find().

Dim myRng As Range
Dim MinDate As Double
Dim res As Variant

With Worksheets("sheet5")
Set myRng = .Range("K2:K80")
If Application.Count(myRng) = 0 Then
MsgBox "no numbers/dates in that range!"
Exit Sub '???
Else
MinDate = Application.Min(myRng)
res = Application.Match(MinDate, myRng, 0)
If IsError(res) Then
MsgBox "min not found!"
Else
myRng(res).Font.ColorIndex = 3
End If
End If
End With




A.S. wrote:

Using Min function, I was able to find the next (soonest) date, but not able
to color the cell. I used MsgBox to see if any address was assigned to
rngFound. Then used rngFound.Cells.Activate to see where the cell was, which
resulted way below the list. What did I do wrong? Here is the code:

Sub FindNext()
Dim myRange As Range
Dim answer As String
Dim rngFound As Range

Set myRange = Worksheets("Sheet5").Range("K2:K80")
answer = Application.WorksheetFunction.Min(myRange)

Set rngFound = myRange(answer)
MsgBox rngFound.Address

answer = Format(answer, "mm/dd/yy")
MsgBox ("The next date is " & answer)
rngFound.Cells.Activate
rngFound.Font.ColorIndex = 3
End Sub

--

Dave Peterson