Auto shading. Is it possible

[sparky3883]

Hi all. I'm knew to this forum (and to excel) so please forgive me i
this is a stupid message or if it's in the wrong place.

I work for a large retail company and I am in charge of doing the dail
staffing rotas. The main rota (template) is done in Excel. We then jus
print of blank copies and fill in the staff names and shifts manually.
Now, for some reason, my managers want the whole rota done on compute
so it looks neater and more professional.

Once i have filled in the staff name and hours that they are workin
each day, i then have to shade in the areas (times) that they are no
working. My rota goes from 6am - 10pm. So, for example, if Joe Bloggs!
works 10am - 4pm, i have to shade in the times that they are no
working (6am - 10am and 4pm - 10pm).

What i was wondering, if it is possible, is, when i enter the time tha
the member of staff is working, is there any chance that these area
can be shaded automatically, instead of me doing it all manually.

I have enclosed a copy of my rota so you can see what i mean.

Any help would be much appreciated as it will save me lots of time.

Thanks in advanc

Attachment filename: blank daily.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46709

 

[Tom Ogilvy]

If you put a character (such as an x) in spaces where a person is working,
then you can use conditional formatting to do the shading automatically.

Select the cells D8 to AJ34 with D8 the active cell

Format=>Conditional formatting

change cell value is to Formula is in the first dropdown. Enter the formula

=AND(COUNTA($D8:$AJ8)>0,D8="")

click on format and select pattern=> light gray or whatever

click OK

This will shade empty cells if at least one cell in the row has a value.

I am not sure what you do with the unlabelled columns between the labelled
columns. I assume you put a character in them as well. If not an you need
them unshaded, then post back with specifics.