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!

 

[sijpie]

What do you mean with 'falls within that week?

 

[TheShaunMichael]

Let's say the dates along the top are Monday 12/28, Monday 1/4, and Monday 1/11. If somebody puts in an event on Tuesday 1/5, I want the Monday 1/4 box to color.

What's complicated is if the user changes the dates along the top from Monday's to say Wednesday, so Wednesday 12/30, 1/6, and 1/13 respectively, I would then need the Wednesday 1/6 box to color because an event on Tuesday the 5th falls in that week.

Hope that's clear.

 

[VoG]

Perhaps

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:75px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">21/12/2009</td><td style="text-align:right; ">25/12/2009</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ff0000; text-align:right; ">25/12/2009</td><td style="text-align:right; ">04/01/2010</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >A2</td><td >1. / Formula is =WEEKNUM(A2)=WEEKNUM(A$1)</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[TheShaunMichael]

Conditional formatting won't work for me in this instance. Any other ideas?

The events in the spreadsheet put in by the user are populated via a form. Once the form command button Okay is clicked a macro runs to color the dates. My thinking was testing to see what day of the week the event falls on and comparing it to what day of the week the dates along the top fall on and writing some code accordingly. But again, that seems tedious.

 

[VoG]

There's some code for calculating week numbers here http://www.cpearson.com/Excel/WeekNumbers.aspx

You may be able to use it to calculate the week numbers of two dates, test for equality and colour cells accordingly.

 

[Bill Rockenbach]

Put this code in the sheet code module
Assumes control date in row 2
if entry date is within week cell turns green otherwise it turns red.
IF you do not want the cell turning green just delete that line of code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ColumnWeekday As Integer
    Dim Udate As Date
    Dim Ldate As Date
    Dim WeekDate As Date
    If Target.Row < 3 Then Exit Sub
    If Not IsDate(Target) Then
        MsgBox "Bad Date"
        Exit Sub
    End If
    WeekDate = Cells(2, Target.Column)
    ColumnWeekday = Weekday(WeekDate)
    Udate = WeekDate + (7 - ColumnWeekday)
    Ldate = Udate - 6
    If Target >= Ldate And Target <= Udate Then
        Target.Interior.ColorIndex = 35
    Else
        Target.Interior.ColorIndex = 3
    End If
End Sub

 

[Bill Rockenbach]

Just saw your post where the cells are updated via a form.

I think you can adopt parts of my previouly posted code to run in your forms macro.

 

[Bill Rockenbach]

Just had a thought. When exiting the contol on your form where the date is entered, do an edit check to verify the date is within the targeted week. If not issue an error and reset focus back to the control.

In otherwords don't allow posting teh information to the worksheet until acceptable date is entered

 

[tusharm]

Maybe, you should indicate what all will and will not work for you. That way, people can focus only on acceptable approaches and you will get your solution that much faster.

Conditional formatting won't work for me in this instance. Any other ideas?

The events in the spreadsheet put in by the user are populated via a form. Once the form command button Okay is clicked a macro runs to color the dates. My thinking was testing to see what day of the week the event falls on and comparing it to what day of the week the dates along the top fall on and writing some code accordingly. But again, that seems tedious.