Find a value and color that cell



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.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.

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 '???
MinDate = Application.Min(myRng)
res = Application.Match(MinDate, myRng, 0)
If IsError(res) Then
MsgBox "min not found!"
myRng(res).Font.ColorIndex = 3
End If
End If
End With


Try the following. Note the comments.

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

Set myRange = Worksheets("Sheet5") _

answer = Application _

Set rngFound = myRange _
.Find(What:=answer, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=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

rngFound.Font.ColorIndex = 3
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question