Changing the colour of a range of cells, some with formulas.

[Champ]

Hi there

I'm hoping someone can assist me. I am wanting a range of cells shaded grey,
one of which includes a formula that specifies its date, and if the date
falls on a Saturday and Sunday they're to be shaded grey. Some more
information that may help. The workbook contains 4 sheets, one for each
quarter, and for each quarter I want to just have to enter one date i.e
01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns
across the sheet are populated by & simple '=c3+1' formula but then if that
date is a Saturday and Sunday, I'd like it and several rows below it, shaded
grey.

Hope this makes sense.
Cheers,
Champ

 

[David Biddulph]

Conditional formatting, using the WEEKDAY function.
Info on both is available in Excel help.

 

[Pete_UK]

You would use conditional formatting to do this - available from the
format menu. You need to choose Formula Is rather than Cell Value Is
in the first box of the pop-up, and then enter this formula:

=WEEKDAY(cell_ref,2)>5

then click on the Format button, select the Patterns tab (for
background colour) and choose grey. Then click OK twice to exit the
dialogue boxes.

Hope this helps.

Pete

 

[Champ]

Thanks Pete, this almost works but by referencing the whole sheet with this
condition turns all cells grey, except for the actual dates that are not
weekend. I don't understand what the ",2" & ">5" means. The conditional
format I'm using is: =WEEKDAY($A$3:$L$40,2)>5, and the cells with the dates
in them are in row 3.
Thanks in advance.
Champ

 

[David Biddulph]

If you don't understand the WEEKDAY function, type the word WEEKDAY into
Excel help.

Secondly, you need only one cell reference in the CF formula condition, not
a range. You will also need to understand the difference between absolute
and relative addressing (which again can be found in Excel help). If you
are trying to format the range A3:L40 based on the content of row 3 in each
column, highlight the range, and if you've got A3 as the active cell, use
the CF/ Formula Is condition =WEEKDAY(A$3,2)>5
If you look in other columns and other rows, you will see that each cell's
CF condition will now be referencing the row 3 cell for the relevant column.

 

[Pete_UK]

I presume you mean that your dates are in column 3, rather than row 3.

Highlight the range A3:L40, starting from A3 (so that it is the active
cell), then click on Format | Conditional formatting. Choose Formula
Is, and then enter this formula:

=WEEKDAY($C3,2)>5

Click on the Format button and then choose grey in the Patterns tab,
then click OK twice to exit. Excel will automatically adjust that cell
reference to suit each row in the highlighted area, and each row will
refer to column C (hence the $C3). This will turn the row from A to L
grey if the date in C is a weekend.

WEEKDAY(cell,2) returns numbers 1 to 7, where 1 = Monday, 2 = Tuesday,
6 = Satuday, 7 = Sunday, etc., so if you are looking for weekends you
want values greater than 5.

Hope this helps.

Pete

 

[Champ]

You've been a great help. Excel help is useful when you know what you're
looking for.
Cheers.

 

[Champ]

Thank you Pete, and I did mean row 3, not column 3. It's all good though,
cheers.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete