Conditional formating

[Brian Bermingham]

Hi
I have a sheet with the following headings:
It's a meant to record holidays for a number of people.
H I J K L M ......AN
Year Month 1 2 3 4 ..... 31
1 2009 1
2 2009 1
3 2009 2
4 2009 2
etc
I am trying to change the background colour of the cells where the month ,
year and day fall on a weekend.

If I put =TEXT(DATE(H2,I2,J1),"ddd") in any cell the appropriate third
parameter I can get the day in the cell.
But when I drag the formula to other cells horizontaly or verticaly I get
changes to H2,I2 etc.

I have also tried conditional formating =TEXT(DATE($H2,$I2,L1),"ddd")="Sat"
This changes the background of a cell with Sat in it.

Can anybody point me in the right direction

Thanks

Brian

 

[Shane Devenshire]

Hi,

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format, it looks like J2:AN100 or so.
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=MOD(DATE($H2,$I2,J$1),7)<2
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=MOD(DATE($H2,$I2,J$1),7)<2
5. Click the Format button and choose a format.
6. Click OK twice

If you are using the 1904 date system you will need to modify this or use
the Weekday function.

 

[Luke M]

Select J2. Format - Conditional Format, change first box to "formula is".
Input the following:

=WEEKDAY(DATE($H2,$I2,J$1),2)>5

Select your formatting as desired.
(The weekday function returns a 6 or 7 in this instance for Sat. & Sun.,
respectively)

 

[Brian Bermingham]

Thanks Luke and Shane

Both suggestions do what I am looking for.

However when I apply the background formating to cells that have contents,
the contents are deleted.
Is there there any way to apply the formating without deleting the contents?

Thanks

Brian