Excel 2007 : Conditional formatting based on another cells contents

Hi,

I have a worksheet with the entire year on it, with date(H1) and day(H2) of the week, and hours worked in the column below(H3:H63). Our pay day is thursday and I want a conditional formula in each cell that will look at the day of the week, if H2=Wed then place a border at the right hand side of H3:H63, to denote end of last week, start of next week.

I want a conditional format so that when I use this sheet next year, I can put in the date in the top right corner, the days will update for the new year, and the lines will reappear in the correct place.

Is it possible, or will I have to use an IF statement in each cell? (Waaa)

Cheers

You can set the conditional formatting in H3:H63 with a formula that looks at H1.

Select the range H3:H63 and use

=WEEKDAY(H$1)=4

format as required

I'm not sure what you entered but the formula is now surrounded with quotes (which you don't want) - that sometimes means you didn't use the = sign.

I cleared the existing conditional formatting and then selected the whole range C3:M50 and used this formula

=WEEKDAY(C$1)=4

Using C$1 means that the formula will adjust to refer to D1, E1 etc across the column but $ fixes the 1 so down the column every cell still looks at C1

Although you have the date in C1 and the day in C2, the cell contents are actually the same - formatting makes one show the full date and the other just the day.....so you can refer to C1 or C2...it doesn't matter

WEEKDAY returns a value 1 to 7, Sunday to Saturday so 4 is a Wednesday, see attached

Change the date in A1 to see how the formatting changes.....