Fill Color

Lambada

Board Regular
Joined
May 10, 2002
Messages
62
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 ?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top