Find a value and color that cell
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
--
Regards,
OssieMac
|