How Can I: Highlight a Range of Cells Based on Dates?

I am hoping that someone can help me solve a riddle regarding the best way to make this objective be reached:

I have a range of empty cells.
Each cell represents 1 week within a calendar year.
Each row contains 52 cells (=1yr).
There are 90 rows.

I want to enter a starting date and then auto-highlight all of the weeks that occur between the start date and today, on that large range of cells described above.

Not sure how to make something like that happen.
Any ideas?

Thanks guys!

Originally Posted by etaf

I have changed your dates to use weeknum()
so we can see what the weeknumbers are
and then use a conditional formatting to test those weeknumbers against the header grid and highlight if greater or = start and less than or = today()

NOT sure what to do about each row that says year 1,2,3,
how does that change

Also note 2016 has Week53 25th Dec to 31st Dec

Thanks for the file. What I see is the entire right side of the 90 year grid turns green. The goal here is to highlight all of the week markers (cells) that occurred between the start date (user-entered) and today. That's why there are 90 years in those rows. For example, if you entered December 12th 1986, then 30 years worth of cells in the 90 year grid would highlight, starting in row 3 and going down from there.

Originally Posted by etaf

does this work
I have worked out the YEAR
taken one from the other to get the number of ROWS to highlight
BUT as the grid starts in row 3 Ihave added 2 in the conditional formatting formula

so ROW() gives me the row number
so test that row number against the difference of the years plus 2 to account for the rows starting at 3

I see that this is much better that the previous, but I don't understand by only the right half highlights in green... Is my Excel differently executing something?

Three rules:

=AND(ROW()+1996=$BE$5,B$2>=$BD$5) for first year (1999)

=(ROW()+1996<$BE$7) for 1997 to 2015 (in your example)

=AND(ROW()+1996=$BE$7,$BD$7>=B$2) for final year

ALL have same fill colour.

Hi there

I am sorry, I just don't see your solution working out. I asked a friend who is not a user of this forum and he eventually put together this, which hits the mark.

Thanks for the work, I appreciate it.