Fill Color

[Lambada]

I have this spreadsheet that is format as follows: B5:AF5 represents the days in the month (1-31). B7:AF30 are the areas for login certain information divided by days of the month. Cell AA3 has the month, and cell AE3 has the year. What I want is for all sundays of the month to be fill colored in gray based on the month and year entered in cells AA3 and AE3. Can this be done ?

 

[Lambada]

Thank you very much Dave! It worked.

 

[pilot]

Here's a variation on what you're asking for. Wish I could take credit but this was developed with much assistance from this board, especially Aladin (thanks again!).

I started with a grid of 1-31 in columns B1:AF1 and JAN-DEC in rows A5:A16. The year is defined elsewhere as Base. I wanted to shade the grid one color for Holidays (as I define them), a second color for Sat & Sun, and a third color for invalid dates like Sep 31.

I have a ws named ValidDates3 similar to the above with 1-31 in B1:AF1 and Jan-Dec in A2:A13. A1 contains the year (2002). Cell C2 contains this formula:

=IF(TEXT(DATE($A$1,MATCH($A2,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"},0),C$1),"mmm")=$A2,DATE($A$1,MATCH($A2,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"},0),C$1),"Invalid")

and it is used to fill the rest of the grid with the exception of defined holidays. Holidays contain the text "Holiday".Each cell in the grid displays the date it references as mm-dd-yy or "Holiday" or "Invalid".

DAYS is defined as =ValidDates3!$B$1:$AF$1

MONTHS is defined as =ValidDates3!$A$2:$A$13

BASE is defined as =ValidDates3!$A$1

Finally, cell B5 in the original ws contains the following Conditional Format:

Condition 1 (dark blue background) identifies invalid dates:
=OFFSET(Base,MATCH(IF(LEN($A5),$A5,$A4),MONTHS,0),MATCH(B$4,DAYS,0))="Invalid"

Condition 2 (gray background) identifies weekends:
=WEEKDAY(OFFSET(Base,MATCH(IF(LEN($A5),$A5,$A4),MONTHS,0),MATCH(B$4,DAYS,0)),2)>5

Condition 3 (dark green background) identifies holidays:
=OFFSET(Base,MATCH(IF(LEN($A5),$A5,$A4),MONTHS,0),MATCH(B$4,DAYS,0))="Holiday"

I hope this helps you as much as it did me.
This message was edited by pilot on 2002-08-26 06:38