Hiding columns with shaded headers
 

Using Excel 2007
Row 1 has a header of calendar dates.
I apply a conditional formatting formula to shade weekend headers in
yellow.
Then I execute the following code to hide columns with shaded headers,
but nothing happens.
..
Sub HideShadedCols()
Dim myRange As Range
For i = 9 To 256
Set myRange = Cells(1, i)
If myRange.Interior.ColorIndex < xlNone Then
myRange.EntireColumn.Hidden = True
End If
Next
End Sub
..
However, for test purpose, if I color some headers cells the hard way
and run my code,
it hides the shaded columns properly.
The question is, since the applied conditional formating is not
recognized by VBA
what intermediate statements do I have to insert ?
Thank you for your help.
J.P.

Hiding columns with shaded headers
 

Hi,

While it's possible to text for a CF colour in VB it's difficult. far easier
to test for the CF format condition and hide columns based upon that

Sub HideShadedCols()
Dim myRange As Range
For i = 9 To 256
Set myRange = Cells(1, i)

If Weekday(myRange) = 1 Or Weekday(myRange) = 7 _
And myRange < "" Then
myRange.EntireColumn.Hidden = True
End If
Next
End Sub

Mike

"u473" wrote:

Using Excel 2007
Row 1 has a header of calendar dates.
I apply a conditional formatting formula to shade weekend headers in
yellow.
Then I execute the following code to hide columns with shaded headers,
but nothing happens.
..
Sub HideShadedCols()
Dim myRange As Range
For i = 9 To 256
Set myRange = Cells(1, i)
If myRange.Interior.ColorIndex < xlNone Then
myRange.EntireColumn.Hidden = True
End If
Next
End Sub
..
However, for test purpose, if I color some headers cells the hard way
and run my code,
it hides the shaded columns properly.
The question is, since the applied conditional formating is not
recognized by VBA
what intermediate statements do I have to insert ?
Thank you for your help.
J.P.

Hiding columns with shaded headers
 

Thank you for this tip.
How to I incorporate in this formula the table of Holidays Dates in
Sheet2 Column A that are in a range called Holidays ?
Thak you again,
J.P.

Hiding columns with shaded headers
 

Hi,

Updated to include holiday range

Sub HideShadedCols()
Dim myRange As Range
For i = 9 To 256
Set myRange = Cells(1, i)
For Each c In Sheets("Sheet2").Range("Holidays")
If myRange = c Then myRange.EntireColumn.Hidden = True
Next
If Weekday(myRange) = 1 Or Weekday(myRange) = 7 _
And myRange < "" Then
myRange.EntireColumn.Hidden = True
End If
Next
End Sub

Mike

"u473" wrote:

Thank you for this tip.
How to I incorporate in this formula the table of Holidays Dates in
Sheet2 Column A that are in a range called Holidays ?
Thak you again,
J.P.

Hiding columns with shaded headers
 

Thanks a lot,
You made my day.
J.P.