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.

 

[sparky3883]

Hi Tom

Many thanks for your reply. I have given it a go but it didn't wor
(maybe i did something wrong-like i said, i am new to this.)
I have just remembered something tht maybe i should have mentioned i
my first post.
Cell A is obviously where the name goes. Cell B is where i want to pu
the shift time. For example, 7 - 4, or 12 - 6, or 6 - 10 etc... When
fill in this Cell with the time, i would like the times not worked, t
be shaded grey.
Is this what your formula suggested. If not, is it at all possible t
do what i want, or am i just being too ambitious

Thanks again

Mar

 

[Tom Ogilvy]

What does 6 - 10 mean. 6 am to 10 am, 6 pm - 10 pm, 6 am - 10 pm

will you always include spaces

If you establish a specific/consistent format for specifying the work
period, that isn't ambiguous, someone might be able to give you a formula
for conditional formatting that works.

If you read my original post, you would see my formula was not based on an
entry in cell B, but marking in the rows the range of cells that would be
worked. I also stated that was my assumption since I don't believe you gave
a clear explanation of what you actually filled in to indicate the work
period.

 

[sparky3883]

Hi Tom. Sorry again for the confusion - i'll try and explain it again.

The shift times will be entered in Cell B, and the shift times wil
vary from 6am - 10pm. An example of the main shifts are:
6am-10am, 7am-4pm, 8am-5pm, 8.30am-5.30pm, 9am-6pm, 10am-2pm, 10am-4pm
12pm-6pm, 1.30pm-10pm, 5pm-10pm and 6pm-10pm.

You asked about spacing?
If spain is easier to understand then yes, i'll use it (6 - 10), nut i
not, i probably won't use it (6-10).

What i am aiming for, is, when i enter a shift time in Cell B, i woul
like the Cells the the member of staff are working to remain white, an
the cells that they are not working to be grey.

As i have two 6-10 shifts (one am and one pm) would i have to go off
24hr clock basis?

I have enclosed an additional rota which i have shaded manually, s
(hopefully) it is easier to understand what i am going on about (sorr
again for the lack of information in my previous posts)

I appreciated you help/input

Many Thank

Attachment filename: ideal rota.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46806

 

[Tom Ogilvy]

Sub ShadeCells
Dim rng as Range, cell as Range
Range("D8:AJ36").Interior.ColorIndex = xlNone
set rng = Range("B8:B36")
For each cell in rng
if not isempty(cell)
select case cell.Text
Case "6~11"
Range("V" & cell.row).Resize(1,23).Interior.ColorIndex = 15
Case "6~12"
Range("P" & cell.row).Resize(1,21).Interior.ColorIndex = 15
Case "6~3"
Range("V" & cell.row).Resize(1,15).Interior.ColorIndex = 15
Case "7~4"
Range("D" & cell.row).Resize(1,2).Interior.ColorIndex = 15
Range("X" & cell.row).Reisze(1,21).Interior.ColorIndex = 15
Case "E"
Range("D" & cell.row).Resize(1,5).Interior.ColorIndex = 15
Range("AA" & cell.row).Reisze(1,10).Interior.ColorIndex = 15
End Select
End if
Next Cell
End Sub

Just add in additional Cases (shifts) as they will appear in column B
consistent with what I have shown above.
Then you can run your macro to do the coloring of the rows. The way you
designate each shift needs to be unique - you can't have duplicate cases or
it will always process it as the first case it matches too.