Staff availability worksheet

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 ?

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)

"John N" <John N@discussions.microsoft.com> wrote in message
news:29AC0374-C4E3-4707-8B1B-9BCBFAC8F84A@microsoft.com...
> 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 ?

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

john@nothere.nealtec.com

"Bob Phillips" wrote:

> 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)
>
> "John N" <John N@discussions.microsoft.com> wrote in message
> news:29AC0374-C4E3-4707-8B1B-9BCBFAC8F84A@microsoft.com...
> > 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 ?
>
>
>

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

Many thanks for your help Bob.

"John N" wrote:

> 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
>
> john@nothere.nealtec.com
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "John N" <John N@discussions.microsoft.com> wrote in message
> > news:29AC0374-C4E3-4707-8B1B-9BCBFAC8F84A@microsoft.com...
> > > 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 ?
> >
> >
> >