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