kilbey1
09-24-2008, 01:42 PM
I've set up this macro that prompts the user for a date, then highlights the rows when it's found in a particular cell. Simple enough.
Can someone please point me in the right direction so I can do the same, except highlight all cells that are >= the date entered?
Sub findDate()
Dim rFound As Range
Dim strDate As String
Dim strFirstAddress As String
Dim lReply As Long
strDate = Application.InputBox(Prompt:="Please Enter Beginning Date to Locate:", Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=2)
'cancel
If strDate = "False" Then Exit Sub
If IsDate(strDate) Then
Rows.Interior.ColorIndex = 0
With ActiveSheet.Range("C:C")
Set rFound = .Find(What:=CDate(strDate), LookIn:=xlValues)
If Not rFound Is Nothing Then
strFirstAddress = rFound.Address
Do
rFound.EntireRow.Interior.ColorIndex = 6
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Address <> strFirstAddress
Else
If rFound Is Nothing Then
lReply = MsgBox("Date cannot be found. Try Again?", vbYesNo)
If lReply = vbYes Then Run "findDate":
End If
End If
End With
'Else
' MsgBox "Invalid Date", vbExclamation
End If
End Sub
Can someone please point me in the right direction so I can do the same, except highlight all cells that are >= the date entered?
Sub findDate()
Dim rFound As Range
Dim strDate As String
Dim strFirstAddress As String
Dim lReply As Long
strDate = Application.InputBox(Prompt:="Please Enter Beginning Date to Locate:", Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=2)
'cancel
If strDate = "False" Then Exit Sub
If IsDate(strDate) Then
Rows.Interior.ColorIndex = 0
With ActiveSheet.Range("C:C")
Set rFound = .Find(What:=CDate(strDate), LookIn:=xlValues)
If Not rFound Is Nothing Then
strFirstAddress = rFound.Address
Do
rFound.EntireRow.Interior.ColorIndex = 6
Set rFound = .FindNext(rFound)
Loop While Not rFound Is Nothing And rFound.Address <> strFirstAddress
Else
If rFound Is Nothing Then
lReply = MsgBox("Date cannot be found. Try Again?", vbYesNo)
If lReply = vbYes Then Run "findDate":
End If
End If
End With
'Else
' MsgBox "Invalid Date", vbExclamation
End If
End Sub