Microsoft Office Forums - View Single Post - Using DateValue to find if a date exists within a certain range

OfficeAssociate99 · 07-13-2017, 07:53 AM

Hi guys, I am trying to search through 5000+ rows to figure out if a date exists within a certain range. I am mostly self-taught, so I have difficulty with assigning correct object variables, etc. This is what I have so far:

Code:

Sub Search()
Dim i As Integer
Dim myDate1 As Integer
Dim myDate2 As Integer
Set r = Range(Range("A2"), Range("AF" & Rows.Count))
i = 2
For Each Row In r
    myDate1 = DateValue(r.Cells(i, 26).Value)
    myDate2 = DateValue(r.Cells(i, 29).Value)
    If myDate1 > 42312 = True And myDate2 > 42461 = True Then
       ThisWorksheet.Rows(i).EntireRow.Interior.ColorIndex = 3
    ElseIf myDate1 > 42460 = True And myDate2 > 42825 = True Then
        ThisWorksheet.Rows(i).EntireRow.Interior.ColorIndex = 3
    End If
    i = i + 1
Next Row
End Sub