Excel confusion

[Guest]

I need to program a column to have a grey background if the top cell in that
column is either a Saturday or a Sunday, but I still need to be able to enter
data in the cells of that column. If the top cell in the column is a weekday,
the column should have no background color.

Is this possible?

 

[Tom Ogilvy]

Sub ColorColumns()
Dim cell as Range
for each cell in Range("A1:Z1")
if isdate(cell) then
if weekday(cell,vbMonday) > 5 then
cell.EntireColumn.Interior.ColorIndex = 15
else
cell.EntireColumn.Interior.ColorIndex = xlNone
end if
end if
Next
End Sub

Change A1:Z1 to reflect the range of cells you want to check.

 

[semiopen]

Hi,

In addition to code as Tom suggested, you could also do this with
conditional formating.

Say you want to do this in column C. Select cell C1 and goto Format
->Conditional Formating. Let the condition be Formula Is

=OR(UPPER($C$1) = "SATURDAY",UPPER($C$1) = "SUNDAY")

then select the desired shade of grey for the corresponding format. Now
- with C1 selected, double click on the Format Painter icon (the
paintbrush) and then select all of column C - press enter then esc.

Hope that helps

-semiopen

 

[semiopen]

A quick follow up:

If your cell header is in date form like 9/29/2006 you could replace
the condition by
=WEEKDAY($C$1,2) >5.