Thread: Create range based off of date

I have set up a workbook for scheduling. I want a msgbox to appear on close if anyone is scheduled to work over 40 hours within a workweek. I have totals on a worksheet called Mon-Sun. The code below works for a named range:

Prompt = "Someone is working over 40 hours! Click Cancel to return to schedule to fix this. Click Ok to continue closing."
    Start = True
     'highlights the over 40 cells
    For Each Cell In Range("mynamedRange")
        If Cell.Value > 40 Then
            Cell.Interior.ColorIndex = 6 '** color yellow
            If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
            Start = False
            RngStr = RngStr & Cell.Address(False, False) & ", "
        Else
            Cell.Interior.ColorIndex = 0 '** no color
        End If
    Next
 If RngStr <> "" Then
        If MsgBox(Prompt & RngStr, vbOKCancel, "Someone is working overtime") = vbCancel Then
        Cancel = True
        End If
    Else

            'saves the changes before closing
        ThisWorkbook.Save
        Cancel = False
   End If

but I only want the msgbox to appear if the person is working over 40 hours in a future workweek. Therefore, I want to set up the range to go from a cell which contains the date of the future monday to the end of the sheet. I thought it would be easy.

This code gets me the cell that holds the correct date (it is on the top row of the worksheet)

If (Weekday(Date) = 2) Then
mydate = Date
Else:
mydate = Date - (8 - Weekday(Date))
End If
Set rCell = Cells.Find(What:=CDate(mydate), After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

The last cell is static: BA27

I thought I could write something like:
Dim TruRng as Range
Set TruRng = Range(rCell, "BA27")

But, I get a run time error 1004 Method Range of _global failed
and it highlights this line of code:
For Each Cell In Range(TruRng)

Thanks for any help!

Sara

As TruRng is a Range, this:

For Each Cell In Range(TruRng)

should be:

For Each Cell In TruRng

Regards

You're welcome. Thanks for the rep.

I tend to find that learning from your mistakes usually means that either you don't make them again or you spot them yourself more quickly if/when you do.

Regards