Conditional cell color

[additude]

I have created a table of 18 X 31 cells with column 1 being a day number from 1 to 31 for days of month and column 2 being the corresponding day name with:

=TEXT("Day-Month-Year","ddd")
=TEXT(""&B6&"-"&$B$3&"-"&$E$3&"","ddd")

Now I want to be able to compare a previous years same day name data highlighted weekly so that I can visually compare previous Mondays to current Mondays, previous Fridays to current Fridays, etc. for the 4 weeks of a month. I think possibly by coloring coding thru a conditional statement.

I am unsure of what might be the best approach. If I can create a formula based on week numbers possibly or if maybe there is a better idea. I would prefer to avoid macros and vb.

Any help is appreciated.

-Wes

 

[pbornemeier]

Please use Excel Jeanie to post some of your worksheet.

What worksheet/cells does your 18 (columns?) by 31 (rows) table for this year occupy?
What worksheet/cells does your 18 (columns?) by 31 (rows) table for last year occupy?
What are the other 16 columns used for?
What data are you trying to compare?
What conditions in the data would cause what format changes?
Given years 2010 and 2011, what days would you be comparing for this week?
How do you define the first week of the year? http://en.wikipedia.org/wiki/ISO_week_date
How do you define the first week of each month?
What is in B6, B3 and E3
What cells do the formulas you posted apply to?

 

[additude]

Thanks for the reply.

I ended up doing:

=TEXT(""&B6&"-"&$B$3&"-"&$E$3&"","ddd")

and conditional formating the cell as:

=MOD((TRUNC(((DATEVALUE(B6&"-"&$B$3&"-"&$E$3)-"1/4/2010")+6)/7)+(WEEKDAY(DATEVALUE(B6&"-"&$B$3&"-"&$E$3))=WEEKDAY("1/4/2010")))/4,1)=0.25

Which =.25 or =.5 or =.75 worked out fine for 4 color transition.