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 ?
>
>
>