AutoFormatting columns

[Bilbo]

Hi
I am trying to format columns based on the content in row
1 which consists of a series of dates. If the date in row
1 is a Saturday or Sunday, I want to shade the whole
column grey. If it is a bank holiday, I want to shade it
blue, for instance.
Anyone got any thoughts?
All help appreciated.
Happy new year

Bilbo

 

[James]

Select the entire area that you will want to automatically
format (say A1:Z100). Use conditional formatting (Format-
Conditional Formatting...).

For Condition 1, choose "Formula is" from the drop down
box. Type:
=OR(WEEKDAY(A$1)=6,WEEKDAY(A$1)=7)
Then click "Format...", and under the Patterns tab choose
grey. If the date in row 1 is a Sat or Sun, the column
will be highlighted as grey.

You will need to identify bank holidays somewhere else (do
this first). On another sheet (say Sheet2) set up a
simple column with dates of holidays, for example:
A1 = 12/25/03
A2 = 12/26/03
A3 = 1/1/04

Then for Condition 2 (back to conditional formatting),
choose "Formula is" and type"
=NOT(ISNA(VLOOKUP(A$1,Sheet2!"A1:A3",1,FALSE)))
Then you can choose blue as you format colour. If the date
in row 1 matches at date on that list, the colum will be
blue.

Hope that works!