AutoFormatting columns

  • Thread starter Thread starter Bilbo
  • Start date Start date
B

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
 
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!
 
Back
Top