Using DateValue to find if a date exists within a certain range

OfficeAssociate99 · #1 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

OfficeAssociate99 · #2 07-13-2017, 10:27 AM

I have figured out the issue, but since there is no way to solve it I will just have to do it all manually...

NoSparks · #3 07-13-2017, 11:05 AM

Quote:

Originally Posted by OfficeAssociate99

I have figured out the issue, but since there is no way to solve it I will just have to do it all manually...

Why not attach a sample workbook indicative of what you have and see what becomes of it ?

OfficeAssociate99 · #4 07-13-2017, 11:06 AM

All the dates that are entered are formatted differently, which is why the DateValue function does not return the correct information...clearly no-one at the office has heard of standardization...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Microsoft Project 2016 print specific date range stuck on one date	martinsalmon	Project	1	10-12-2016 11:36 AM
Find if Date range falls within another range	Triadragon	Excel	3	05-02-2016 11:48 AM
Find a Date in a Range	rspiet	Excel	3	02-15-2016 08:37 AM
Check if a Date falls within a date range	OTPM	Excel	7	02-03-2016 09:11 PM
find IP in range / find number between numbers	gn28	Excel	4	06-14-2015 03:46 PM