Colour Coding Cells If Contracted Hours Not Met

**suma** · 01-03-2012, 05:26 PM

Hi,
I want to create a time card which will generate colour coded warning if someone works either more, or less than their weekly contracted minuets.

Required:

If someone works less than 2,250 minutes (37.5 Hours), then wherever that employees name appears in the 5 time cards, the cell glows RED
If someone works more than 2,250 minutes (37.5 Hours), then cell glows GREEN
If someone works 2,250 minutes (37.5 Hours) exactly, then cell remains unchanged

The cell to be coloured is the cell in column B containing the name

Cells which have been greyed out will be hidden from view

Please see attached sample sheet.

Many Thanks,

Sean

**Bob Phillips** · 01-03-2012, 05:30 PM

Use conditional formatting with formulae of

=SUMIF($X:$X,$X6,$AH:$AH)<2250

and

=SUMIF($X:$X6,$AH:$AH)>2250

**Fotis1991** · 01-03-2012, 06:25 PM

Hi

If i understoond well your point, then AH6>2250=1ST CONDITION and AH6<2250, IS THE SECOND CONDITION.

Hope to helps you.

**suma** · 01-03-2012, 07:40 PM

Thanks for your sugestions. Unfortunatly, I uploaded the wrong version of the sheet. I have replaced it with the correct sheet, and I would be grateful if you could take another look.

As one employee could be moved from one machine to another half way through a shift, what I really need is the color coding to apply to the total mins worked through the week, being 2250 mins.

Thanks