Hi.

I have a spreadsheet setup as follows:

Week commencing dates on separate rows on the left;
In the columns, I have the time of day, from 09:00 to 17:30, split into 15 minute intervals;

The aim is for the sheet to be completed as follows:

Each piece of work has a unique letter / number code. When an employee enters an "L", for example, the cell should colour in green which will signify a period of lunch. What I'd ideally like to do is, if an "L" is entered in any cell, within the range C11:AK29, the cell will automatically colour green.

Similarly, if a "1" is entered anywhere, the cell should colour Blue, for example, which will signify a team meeting etc.

I've tried setting rules up, with my latest attempt being:

Highlight the area C11:AK29;
Create a new rule, using a formula to determine which cells to format;
Format values where the following formula is true: =$C$11:$AK$29="L";
Format: Green background
Applies to: =$C$11:$AK$29;

Which hasn't worked. I've tried shifting the values around, but I still can't get it working. I either get no formatting at all, or the entire area is coloured green!

Can someone help?

Duncan

Hi Duncan,

Am Tue, 9 Dec 2014 03:48:36 -0800 (PST) schrieb Duncan Edment:

Highlight the area C11:AK29;
Create a new rule, using a formula to determine which cells to format;
Format values where the following formula is true: =$C$11:$AK$29="L";
Format: Green background
Applies to: =$C$11:$AK$29;

the formula has to refer to the active cell of the selection
Try it with:
=C11="L"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Tuesday, 9 December 2014 11:54:03 UTC, Claus Busch wrote:
Hi Duncan,

Am Tue, 9 Dec 2014 03:48:36 -0800 (PST) schrieb Duncan Edment:

Highlight the area C11:AK29;
Create a new rule, using a formula to determine which cells to format;
Format values where the following formula is true: =$C$11:$AK$29="L";
Format: Green background
Applies to: =$C$11:$AK$29;

the formula has to refer to the active cell of the selection
Try it with:
=C11="L"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Perfect Claus. Just perfect.

Cheers

Duncan