Excel confusion
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? |
Excel confusion
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. -- Regards, Tom Ogilvy "Syrena" wrote in message ... 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? |
Excel confusion
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 Syrena wrote: 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? |
Excel confusion
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. semiopen wrote: 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 Syrena wrote: 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? |
All times are GMT +1. The time now is 10:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com