Conditional formatting in Excel 2010

  • Thread starter Thread starter Duncan Edment
  • Start date Start date
D

Duncan Edment

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 signifya 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;
Click to expand...

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


Regards
Claus B.
 
Hi Duncan,

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


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


Regards
Claus B.
Click to expand...

Perfect Claus. Just perfect.

Cheers

Duncan
 
Back
Top