Excel: Colouring Cells If Weekend 1

[AirZebra]

Hi All,

I have been given a spreadsheet which lists month names across the top in row 2, and days (1 - 31) down the side in Column 2 (the year name is in the worksheet tab). For example Sheet 2002!D4 is equivalent to 2nd February 2002.

I have been "asked" to change the colour of the background of the cells which correspond to weekends.

We have approx. 40 books containing 5 sheets/years worth of data.

Can anyone give me any ideas to get me started?

Regards,

Alan

 

[oharab]

Ok then! This uses conditional formatting to do what you want.
It can be a bit slow updating, but it will always be right!

Select the top left cell in your data grid (ie 1st January)

Goto format->Conditional Formatting. Change the option to "Formula is" and enter this function:
=OR(WEEKDAY(DATE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),COUNTA($C$2:C$2),$B3))=7,WEEKDAY(DATE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),COUNTA($C$2:C$2),$B3))=1)

Then change the formatting of the cells to whatever you want, then click add, then ok.

Now copy cell c3, select all the sheets you want to change, then select the calendar.

Choose Edit->Paste Special and choose the formats option and press ok.

Job done!!

This works fine on my XL2k, but should work fine on others too.

Let me know how you get on.
Cheers

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[TopJack]

I have quickly wrote the code to update an active sheet to start you off.

************************************************

Sub find_weekends()
Dim user_day As Integer, user_month As Integer, user_date As String, weekend As Integer

For user_month = 1 To 12
For user_day = 1 To 31
user_date = user_day & "/" & user_month & "/" & ActiveSheet.Name
If (IsDate(user_date)) Then
weekend = WeekDay(DateValue(user_date))
If (weekend = 1 Or weekend = 7) Then
Cells(user_day + 2, user_month + 2).Interior.ColorIndex = 4
End If
End If
Next user_day
Next user_month

End Sub

************************************************

Note: the user_date is based around your systems date format. You might need to alter this.

Hope this helps you on your way.

 

[AirZebra]

Ben, I couldn't manage to get your formatting to work, was this because there is nothing actually in the cells themselves (can you tell I couldn't follow it)?

TopJack, Thanks for that, that worked fine, a star from me.

Regards,

Alan