View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
OssieMac OssieMac is offline
external usenet poster
 
Posts: 2,510
Default 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