Recognize & highlight weekend dates from formula result

[AE25]

Hi All,

Need to prepare a schedule (for monthly, fortnightly & weekly)

Few things to work out
1. There are 8 task in a month, Deadline is always 14th of the month unless it's weekend then it needs to move to friday
2. There are 6 task prior to deadline which are based on
i. Task 6 = deadline - 1 day
ii. Task 5 = same as Task 6
iii. Task 4 = task 4 - 1 day
and likewise

Above can somehow be managed however, want to highlight within the range of D9 : K20 where dates are Sat / Sun to color background of cell red via macro. Note: As they are not consecutive dates the conditional formatting didn't work.

Purpose is the to highlight and user to manual change the dates before finalizing the schedule. after weekend dates are changed when we click button it needs undo red, coz they are no longer weekend date.

Hope this makes sense.

Attached sample spreadsheet.

Appreciate if someone could look into this and advise resolution.

[JBeaucaire]

1) Highlight D9:K20
2) Apply conditional formatting rule of:

=OR(WEEKDAY(D9)=1, WEEKDAY(D9)=7)

[AE25]

Tried above steps didn't work. in-spite of highlight D9 to K20, the formula only understands D9 cell and works on that cell address. how would it understand the all 96 cells I want to apply. Definitely I'm missing some link.
However, please note, when go to individual cell and apply the formula it does. Surely, there's might be another way to do rather than applying this condition for each cell.

Appreciate any assistance.

[JBeaucaire]

If you follow the steps exactly, it works for the entire range. You have to click on D9 first so it's the "anchor" cell, then stretch to highlight the entire range.

Now when you enter the CF formula using D9 as the selected anchor for the entire range, the formula self-adjusts for all the cells.

Video example: http://screencast.com/t/zZXO8r9k5VJN

[AE25]

It worked....really appreciate your help.

[JBeaucaire]

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.