Staff availability worksheet

[Guest]

I have set up a basic staff availablility worksheet in excel for my team.
Basically it has staff names down the right side and a timeline across the
top. Staff select an appropriate range of cells adjacent thier name and use a
colour code to indicate meetings, leave etc. We currently do this by
selecting "Format" then "patterns" and clicking on a colour. We also have a
"Key" section on the worksheet which shows which colour should be used for
what.
It would be a lot easier if we could select the range of cells and then just
click on the appropriate colour in the "key" section, and have that colour
then applied to the selected range of cells.

Is this possible ?

 

[Bob Phillips]

John,

Here is a way.

Assuming that the blocks are B2:Q10 (you can configure this in the code),
and you have a key table in column U with the colours, and some key value to
associate with the colours, such as M for meetings, L for Leave, etc.. When
you want to allocate a block, select all the cells, enter the time id (M, L
etc.) then hit Ctrl-Enter. This loads the value in all the cells. Add this
code to process that input

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:Q20"
Dim cell As Range
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
On Error Resume Next
iPos = Application.Match(cell.Value, Columns("U:U"), 0)
On Error GoTo 0
If iPos > 0 Then
cell.Interior.ColorIndex = Cells(iPos,
"U").Interior.ColorIndex
cell.Font.ColorIndex = Cells(iPos, "U").Interior.ColorIndex
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hello Bob

Sorry not to get back to you earlier, but I am not that familiar with using
VB, and I have not been able to make it work. I copied the code as suggested
and modified the range details to match the ones used in my sheet - but with
no success.

I know its a lot to ask - but is there any chance you could have a look at
the worksheet and see what I am doing wrong. It sounds like such a good
solution, I would love to get it to work.

John

(e-mail address removed)

 

[Guest]

Ooops ! Spotted my mistake - the solution is now working fine.

Many thanks for your help Bob.