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 ?

 

[Legacy 7259]

Select B1:AF5, go to ConditionalFormatting, type =WEEKDAY(DATE($AE$5,$AA$5,B5))=1 in the FormulaIs box and select the cell colour you want.

 

[Lambada]

Thank you very munch! The only problem I am having now is that on FEBRUARY the 29th - 31 does not get color. Any ideas?

Again, Thank you very munch.

 

[Jack in the UK]

ermm

how many days does Feb really have???

YES for a laugh i did VBA a sheet to except 30 and 31 feb caused a load of agro, but i thought it was funny!

 

[Lambada]

...Every 4 years 29 days Would I be able to make this formula work?

 

[Dave Patton]

On 2002-08-24 09:44, Lambada wrote:
...Every 4 years 29 days Would I be able to make this formula work?

try Conditional format
with Formula is

=WEEKDAY(DATE($AE$3,$AA$3,B5),1)=1

and to turn off

=WEEKDAY(DATE($AE$3,$AA$3,B5),1)>1

Use this on B5 and use Format Painter to copy to others.

Ensure that the ranges are correct.

I do not understand why you think February 29 will be a problem.

 

[Lambada]

Thank you again Dave. Got it working, but the problem is that I want to fill color Sundays and 31st, 30th and of course February 29's that does not exist. You covered Sundays already. This is an operating log of equipment. Sundays we are close and there are no such thing as September 31 and so on. Sorry that I didn't explained my self-better on earlier posts. Can it be done?

Thank very much!
This message was edited by Lambada on 2002-08-24 23:41

 

[Dave Patton]

On 2002-08-24 23:40, Lambada wrote:
Thank you again Dave. Got it working, but the problem is that I want to fill color Sundays and 31st, 30th and of course February 29's that does not exist. You covered Sundays already. This is an operating log of equipment. Sundays we are close and there are no such thing as September 31 and so on. Sorry that I didn't explained my self-better on earlier posts. Can it be done?

Thank very much!
This message was edited by Lambada on 2002-08-24 23:41

Did you try to extend the formula to cover that condition?

try
=and(WEEKDAY(DATE($AE$3,$AA$3,B5),1)=1,(MONTH(DATE($AE$3,$AA$3,B5))=$AA$3))

Second condition
=OR(WEEKDAY(DATE($AE$3,$AA$3,B5),1)=1,MONTH(DATE($AE$3,$AA$3,AF5))=$AA$3)

Format the FONT white and clear background format. Then numbers like 29, 30, 31 that are not in a particular month will not be visible.
This message was edited by Dave Patton on 2002-08-25 02:13

 

[Lambada]

I am getting closer. Now it does whites the font on Sundays, but like on February 2002 I’m still having the same results. They are not being color or invisible. What am I doing wrong? Would it be the format I’m using on AA3 or AE3? On AA3 I have just a number "2" and on AE3 the year "2002". Both of them format are set as general.
This message was edited by Lambada on 2002-08-25 09:30

 

[Dave Patton]

On 2002-08-25 08:49, Lambada wrote:
I am getting closer. Now it does whites the font on Sundays, but like on February 2002 I’m still having the same results. They are not being color or invisible. What am I doing wrong? Would it be the format I’m using on AA3 or AE3? On AA3 I have just a number "2" and on AE3 the year "2002". Both of them format are set as general.
This message was edited by Lambada on 2002-08-25 09:30

What formula did you use???


Check the formulas. You could check the result of each part of the formula i the regular part of the spreadsheet.

The following 2 formulas are in cell for AD5

=AND(WEEKDAY(DATE($AE$3,$AA$3,AD5),1)=1,MONTH(DATE($AE$3,$AA$3,AD5))=$AA$3)

-- formatted with shading

=OR(WEEKDAY(DATE($AE$3,$AA$3,AD5),1)=1,NOT(MONTH(DATE($AE$3,$AA$3,AD5))=$AA$3))

-- formatted font white and use "clear" option at bottom right of format tab.
This message was edited by Dave Patton on 2002-08-25 09:40