search for date range - whole year

[woon8888]

i have a list of dates below that are restricted date & time. i will need to program a VBA that will detected if the entered dates fall between the restricted date/time if it falls between then an alert /msgbox will popup stating it's restricted date & time.

I've written the below code. but it only works on the same sheet. i cannot reference it if it's different sheet.

Any experts to help?

Sub searchDate()
Dim strDate As String
Dim rCells As Range
Dim dCells As Date
Dim tidetbl As Worksheet

strDate = Range("I1").Value
dCells = Range("I1").Value

Set tidetbl = ThisWorkbook.Worksheets("Restricted Date")

strDate = Format(strDate, "Short Date")

Set rCell = Cells.Find(What:=CDate(strDate), After:=tidetbl.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If dCells >= rCell.Offset(0, 1) And dCells <= rCell.Offset(0, 2) Or dCells >= rCell.Offset(0, 3) And dCells <= rCell.Offset(0, 4) Then
    MsgBox ("Restricted Date")
Else
    MsgBox ("OK Date")
End If
End Sub

For e.g. if the dates is 03/Jan 01:23 HRS then it's within the date Restricted date start and end.

below is only 2 months for reference. the actual one has got whole year which is not feasible to paste everything here.

	Date Start	Date End	Date (2) Start	Date (2) End
1-Jan-23
2-Jan-23
3-Jan-23	03-Jan 01:00	03-Jan 06:00
4-Jan-23	04-Jan 00:00	04-Jan 07:00
5-Jan-23	05-Jan 00:00	05-Jan 08:00
6-Jan-23	06-Jan 01:00	06-Jan 08:00
7-Jan-23	07-Jan 02:00	07-Jan 09:00
8-Jan-23	08-Jan 04:00	08-Jan 09:00
9-Jan-23	09-Jan 05:00	09-Jan 10:00
10-Jan-23	10-Jan 05:00	10-Jan 11:00
11-Jan-23	11-Jan 06:00	11-Jan 11:00
12-Jan-23	12-Jan 07:00	12-Jan 12:00
13-Jan-23	13-Jan 10:00	13-Jan 11:00
14-Jan-23
15-Jan-23
16-Jan-23
17-Jan-23
18-Jan-23	18-Jan 00:00	18-Jan 05:00	18-Jan 23:00	18-Jan 23:59
19-Jan-23	19-Jan 00:00	19-Jan 06:00	19-Jan 23:00	19-Jan 23:59
20-Jan-23	20-Jan 00:00	20-Jan 07:00	20-Jan 14:00	20-Jan 15:00
21-Jan-23	21-Jan 00:00	21-Jan 08:00	21-Jan 14:00	21-Jan 17:00
22-Jan-23	22-Jan 02:00	22-Jan 09:00	22-Jan 15:00	22-Jan 18:00
23-Jan-23	23-Jan 04:00	23-Jan 10:00	23-Jan 16:00	23-Jan 18:00
24-Jan-23	24-Jan 05:00	24-Jan 11:00
25-Jan-23	25-Jan 07:00	25-Jan 12:00
26-Jan-23	26-Jan 10:00	26-Jan 12:00
27-Jan-23
28-Jan-23
29-Jan-23
30-Jan-23	30-Jan 22:00	30-Jan 23:59	30-Jan 22:00	30-Jan 23:59
31-Jan-23	31-Jan 00:00	31-Jan 04:00	31-Jan 22:00	31-Jan 23:59
1-Feb-23	01-Feb 00:00	01-Feb 05:00	01-Feb 22:00	02-Feb 00:00
2-Feb-23	02-Feb 00:00	02-Feb 06:00	02-Feb 23:00	03-Feb 00:00
3-Feb-23	03-Feb 00:00	03-Feb 07:00
4-Feb-23	04-Feb 01:00	04-Feb 08:00
5-Feb-23	05-Feb 03:00	05-Feb 09:00
6-Feb-23	06-Feb 04:00	06-Feb 10:00
7-Feb-23	07-Feb 06:00	07-Feb 10:00
8-Feb-23	08-Feb 07:00	08-Feb 11:00
9-Feb-23	09-Feb 08:00	09-Feb 11:00

 

[AlphaFrog]

Sub searchDate()
Dim strDate As String
Dim rCells As Range
Dim dCells As Date
Dim tidetbl As Worksheet

' Qualify the sheet that has the input dates.
strDate = Sheets("Input").Range("I1").Value
dCells = Sheets("Input").Range("I1").Value

Set tidetbl = ThisWorkbook.Worksheets("Restricted Date")

strDate = Format(strDate, "Short Date")

' Qualify the "Restricted Date sheet
Set rCell = tidetbl.Columns("A").Find(What:=CDate(strDate), After:=tidetbl.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

' Add parenthisis to group the two AND criteria
If (dCells >= rCell.Offset(0, 1) And dCells <= rCell.Offset(0, 2)) Or (dCells >= rCell.Offset(0, 3) And dCells <= rCell.Offset(0, 4)) Then
    MsgBox ("Restricted Date")
Else
    MsgBox ("OK Date")
End If
End Sub

 

[woon8888]

This worked like a charm! Kudos! @AlphaFrog