Date falling within a week

[TheShaunMichael]

Along the top of my spreadsheet are dates that fall on the same day every week. However, the day of the week is not always a Monday or a Tuesday. It depends on a different user input.

When the user puts in a new event down below and selects the dates it will fall on, the appropriate cells color below the appropriate dates (assuming the dates are the same).

What I need to figure out is how to make the cell color if the date of the new event falls in that week. I have an idea on how to do it involving select case but it would be tedious. Hoping someone has a better idea!

 

[TheShaunMichael]

It seems like building a "week number" string into my code will do the trick. What's the best way to test to see if two dates are in the same week. Again, this is going to go into the command button "Okay" code on my user form.

 

[Bill Rockenbach]

The WEEKNUM() function was not included in excel prior to 2007. It is available in ttheAnalysis Tool Pack.

If your using xl2007 or prior versions with the Analysis Tool PAck installed then you can use this code. It uses the WeekNum() function.

Sub CompareDates()
    Dim Mydate As Date
    Dim CompareDate As Date
    Mydate = "8/12/09"          'dates can be assigned from a range, a textbox. a listbox, etc
    CompareDate = "9/12/09"
    If WorksheetFunction.WeekNum(Mydate) = WorksheetFunction.WeekNum(CompareDate) Then
        MsgBox "There the same week"
    Else
        MsgBox "There not the same week"
    End If
End Sub

YOu can use this code with any version. It first finds the beging date of teh week and then the ending date of teh week. If teh date to be compared is within this range then the compare date is in the same week as MyDate.

Sub CompareDates()
    Dim Udate As Date
    Dim Ldate As Date
    Dim Mydate As Date
    Dim CompareDate As Date
    Mydate = "8/12/09"          'dates can be assigned from a range, a textbox. a listbox, etc
    CompareDate = "9/12/09"
    'Find the begining date of the week
    Udate = Mydate + (7 - Weekday(Mydate))
    'find the last date of the week
    Ldate = Udate - 6
    If CompareDate >= Ldate And CompareDate <= Udate Then
        MsgBox "There the same week"
    Else
        MsgBox "There not the same week"
    End If
End Sub