Solved: How do I get excel to change the colour of cells dependant on values in other cells?

[razier]

Hi all, I am new here so firstly let me apologise if this has already been answered but I could not find an answer for this specific problem.

I am using Excel 2007 but saving the workbook as a '03 compatible workbook as we still use '03 at work.

The problem:

I am creating a timesheet that can be as automated as possible to include various different aspect and the sticking point for me right now is the need to change a defined number of cells into a different colour to represent weekends in a month.

This would be quite straight forward for me but the timesheet is a calendar monthly sheet which means that the start day is not a constant so that throws out the idea of just manually changing the colours to suit.

I would also like the colours of Saturday and Sunday to be different as some staff might work on a Saturday but never on a Sunday.

I know what I am trying to achieve which is to get excel to read a cell and define the day entered in it by a date formula I am using and then from there, change the colour of the 36 cells below it.

I can basically describe the command I want a formula or macro to complete in plain english but my knowledge of VBA is extremely limited an I am guessing that this kind of problem needs to be addressed by VB rather than a basic in cell formula.

Any help anyone can offer would be greatly received and if needed I do not mind forwarding a copy of what is there so far to anyone who can help.

A big thank you in advance.

[Bob Phillips]

You would use conditional formatting (http://www.contextures.com/xlCondFormat01.html) with formulae of

=WEEKDAY(A1)=1

and

=WEEKDAY(A1)=7

assuming that the cells are real dates. If you are not having real dates in the cells to be formatted, tell us how the data is laid out and we can adapt it.

[razier]

Thanks for the reply. I am quite a novice at this and I am not too sure how to implement the conditional formatting in such a way that it would work on the sheet I am developing.

I have added the basic spreadsheet on here now so that if you wouldn't mind, you could have a quick look and explain to me exactly how it would work on it.

For example: The formula would have to read the data from Cell H3, J3 and each second cell in the series thereafter. It would have to determine whether the cell contains Saturday or Sunday and if it does, it would then need to alter the colours of the 36 cells (some of which are merged) directly below it.

Again thank you for your help. Sorry for being a pain!

[Bob Phillips]

What colours for Sat and Sun.

[razier]

Light blue Sat and plan red for Sun....

[Bob Phillips]

.

[razier]

Many many thanks xld!

You have saved me hours of messing about and for that I am truly grateful :)