Find a value and color that cell

[A.S.]

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]

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

 

[OssieMac]

Try the following. Note the comments.

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

Set myRange = Worksheets("Sheet5") _
.Range("K2:K80")

answer = Application _
.WorksheetFunction.Min(myRange)

Set rngFound = myRange _
.Find(What:=answer, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

MsgBox ("The next date is " & _
Format(answer, "mm/dd/yy"))

'because this code could be run when
'another worksheet is active you must
'ensure the correct worksheet is active
'before activating cells.
'Also Activate is not necessarily the same
'as Select. You can Activate a cell in
'a selected range.(It is the cell that is
'still white color.) Select a cell means
'it is the only selection

Worksheets("Sheet5").Select
rngFound.Cells.Activate
rngFound.Font.ColorIndex = 3
End Sub