Required Day of week pattern help

[sarfarazbutt]

I have below scenario in one sheet , if user select Y in yellow cell mean weekend, and in whole work book some cell are Sun,Mon,Tue,Wed,Thr,Fri,Sat (these are formulated by date i.e. if I right 1.1.2011 day cell will chose the Sat. What I want once user select the Y in below area all sheets same day will be highlighted in gray colour.

Sunday Monday Tuesday Wednesday Thursday Friday Saturday
1 2 3 4 5 6 7
N N N N N Y Y


Example in all sheet, is there any macro or need conditional formating
6.1.12 7.1.12
Fri Sat

 

[p45cal]

Suppose you have this in a sheet called Control:

Excel Workbook
	A	B	C	D	E	F	G
1	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2	1	2	3	4	5	6	7
3	y	y	N	N	N	N	N
Control

The important bit is only the range A3:G3
Paste the following code into that sheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Intersect(Target, Range("[COLOR=Red]A3:G3[/COLOR]"))
If Not Rng Is Nothing Then
    GreyDays = Application.Transpose(Application.Transpose(Sheets("Control").Range("A3:G3").Value))
    For i = 1 To UBound(GreyDays)
        GreyDays(i) = UCase(GreyDays(i)) = "Y"
    Next i

    For Each sht In ThisWorkbook.Sheets
        For Each cll In sht.UsedRange.Cells
            If TypeName(cll.Value) = "Date" Then
                'remove existing highlight
                cll.Font.ColorIndex = xlAutomatic
                'highlight it according to control sheet
                If GreyDays(Weekday(cll.Value)) Then cll.Font.ColorIndex = 48    '15
            End If
        Next cll
    Next sht
End If
End Sub

I think this is preferable to conditional formatting since you would have to apply it to every cell in the workbook and that could make for a very resource hungy workbook.

I hope your workbook isn't too massive!

You have to edit a cell(s) in range A3:G3 for the macro to run.

 

[sarfarazbutt]

Thanks a lot to write code for me, I am pasting through Alt+F11 in new module but macro not run, is there any specific place i need to do

 

[sarfarazbutt]

Many thanks for your help

if I need to do change the colour of cell instead of word,
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End Sub

how i use this in this case

 

[p45cal]

First reset any existing dates with grey text to normal by allowing the macro to run with all days set to "N". Failing to do this will mean that some dates might be invisible (grey on grey).
Then in the code, substitute the two instances of
Font
for:
Interior

The code needs to be pasted as follows:
Right-click the tab of the Control sheet and choose View Code, then where the cursor is flashing, paste the code.

By the way, the sheet doesn't have to be called Control!

 

[sarfarazbutt]

receiving error Run-time 1004 (below line in red cell)

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Intersect(Target, Range("e45:k45"))
If Not Rng Is Nothing Then
GreyDays = Application.Transpose(Application.Transpose(Sheets("Parameters").Range("e45:k45").Value))
For i = 1 To UBound(GreyDays)
GreyDays(i) = UCase(GreyDays(i)) = "Y"
Next i

For Each sht In ThisWorkbook.Sheets
For Each cll In sht.UsedRange.Cells
If TypeName(cll.Value) = "Date" Then

cll.Interior.ColorIndex = xlAutomatic
If GreyDays(Weekday(cll.Value)) Then cll.Interior.ColorIndex = 48 '15
End If
Next cll
Next sht
End If
End Sub


See the color of cell E52, I want set same in above macro

Excel Workbook
	E	F	G	H	I	J	K
44	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
45	N	N	Y	Y	N	N	N
46	*	*	*	*	*	*	*
47	*	*	*	*	*	*	*
48	*	*	*	*	*	*	*
49	1/11/2011	1/12/2011	1/13/2011	1/14/2011	1/15/2011	*	*
50	Tue	Wed	Thu	Fri	Sat	*	*
51	*	*	*	*	*	*	*
52	0	*	*	*	*	*	*
53	*	*	*	*	*	*	*
54	*	*	*	*	*	*	*
Parameters

 

[p45cal]

Well it works here fine in xl2010 and xl2003.
When the error occurs, choose Debug, then in the immediate pane (Ctrl+G if it's not visible) type:
Application.Goto cll
The cell it failed on is now selected. Anything unusual about this cell?
You're at least on version xl2007 I take it?

 

[sarfarazbutt]

Many thanks its done.

 

[p45cal]

What was wrong?

Many thanks its done.