software

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 quot;Formatquot; then quot;patternsquot; and clicking on a colour. We also have a
quot;Keyquot; 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 quot;keyquot; 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 = quot;B2:Q20quot;
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(quot;U:Uquot;), 0)
On Error GoTo 0
If iPos gt; 0 Then
cell.Interior.ColorIndex = Cells(iPos,
quot;Uquot;).Interior.ColorIndex
cell.Font.ColorIndex = Cells(iPos, quot;Uquot;).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)

quot;John Nquot; lt;John gt; wrote in message
...
gt; I have set up a basic staff availablility worksheet in excel for my team.
gt; Basically it has staff names down the right side and a timeline across the
gt; top. Staff select an appropriate range of cells adjacent thier name and
use a
gt; colour code to indicate meetings, leave etc. We currently do this by
gt; selecting quot;Formatquot; then quot;patternsquot; and clicking on a colour. We also have
a
gt; quot;Keyquot; section on the worksheet which shows which colour should be used for
gt; what.
gt; It would be a lot easier if we could select the range of cells and then
just
gt; click on the appropriate colour in the quot;keyquot; section, and have that colour
gt; then applied to the selected range of cells.
gt;
gt; 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
quot;Bob Phillipsquot; wrote:

gt; John,
gt;
gt; Here is a way.
gt;
gt; Assuming that the blocks are B2:Q10 (you can configure this in the code),
gt; and you have a key table in column U with the colours, and some key value to
gt; associate with the colours, such as M for meetings, L for Leave, etc.. When
gt; you want to allocate a block, select all the cells, enter the time id (M, L
gt; etc.) then hit Ctrl-Enter. This loads the value in all the cells. Add this
gt; code to process that input
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Const WS_RANGE As String = quot;B2:Q20quot;
gt; Dim cell As Range
gt; Dim iPos As Long
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; For Each cell In Target
gt; On Error Resume Next
gt; iPos = Application.Match(cell.Value, Columns(quot;U:Uquot;), 0)
gt; On Error GoTo 0
gt; If iPos gt; 0 Then
gt; cell.Interior.ColorIndex = Cells(iPos,
gt; quot;Uquot;).Interior.ColorIndex
gt; cell.Font.ColorIndex = Cells(iPos, quot;Uquot;).Interior.ColorIndex
gt; End If
gt; Next cell
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;John Nquot; lt;John gt; wrote in message
gt; ...
gt; gt; I have set up a basic staff availablility worksheet in excel for my team.
gt; gt; Basically it has staff names down the right side and a timeline across the
gt; gt; top. Staff select an appropriate range of cells adjacent thier name and
gt; use a
gt; gt; colour code to indicate meetings, leave etc. We currently do this by
gt; gt; selecting quot;Formatquot; then quot;patternsquot; and clicking on a colour. We also have
gt; a
gt; gt; quot;Keyquot; section on the worksheet which shows which colour should be used for
gt; gt; what.
gt; gt; It would be a lot easier if we could select the range of cells and then
gt; just
gt; gt; click on the appropriate colour in the quot;keyquot; section, and have that colour
gt; gt; then applied to the selected range of cells.
gt; gt;
gt; gt; Is this possible ?
gt;
gt;
gt;

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

Many thanks for your help Bob.

quot;John Nquot; wrote:

gt; Hello Bob
gt;
gt; Sorry not to get back to you earlier, but I am not that familiar with using
gt; VB, and I have not been able to make it work. I copied the code as suggested
gt; and modified the range details to match the ones used in my sheet - but with
gt; no success.
gt;
gt; I know its a lot to ask - but is there any chance you could have a look at
gt; the worksheet and see what I am doing wrong. It sounds like such a good
gt; solution, I would love to get it to work.
gt;
gt; John
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; John,
gt; gt;
gt; gt; Here is a way.
gt; gt;
gt; gt; Assuming that the blocks are B2:Q10 (you can configure this in the code),
gt; gt; and you have a key table in column U with the colours, and some key value to
gt; gt; associate with the colours, such as M for meetings, L for Leave, etc.. When
gt; gt; you want to allocate a block, select all the cells, enter the time id (M, L
gt; gt; etc.) then hit Ctrl-Enter. This loads the value in all the cells. Add this
gt; gt; code to process that input
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; Const WS_RANGE As String = quot;B2:Q20quot;
gt; gt; Dim cell As Range
gt; gt; Dim iPos As Long
gt; gt;
gt; gt; On Error GoTo ws_exit:
gt; gt; Application.EnableEvents = False
gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; For Each cell In Target
gt; gt; On Error Resume Next
gt; gt; iPos = Application.Match(cell.Value, Columns(quot;U:Uquot;), 0)
gt; gt; On Error GoTo 0
gt; gt; If iPos gt; 0 Then
gt; gt; cell.Interior.ColorIndex = Cells(iPos,
gt; gt; quot;Uquot;).Interior.ColorIndex
gt; gt; cell.Font.ColorIndex = Cells(iPos, quot;Uquot;).Interior.ColorIndex
gt; gt; End If
gt; gt; Next cell
gt; gt; End If
gt; gt;
gt; gt; ws_exit:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;John Nquot; lt;John gt; wrote in message
gt; gt; ...
gt; gt; gt; I have set up a basic staff availablility worksheet in excel for my team.
gt; gt; gt; Basically it has staff names down the right side and a timeline across the
gt; gt; gt; top. Staff select an appropriate range of cells adjacent thier name and
gt; gt; use a
gt; gt; gt; colour code to indicate meetings, leave etc. We currently do this by
gt; gt; gt; selecting quot;Formatquot; then quot;patternsquot; and clicking on a colour. We also have
gt; gt; a
gt; gt; gt; quot;Keyquot; section on the worksheet which shows which colour should be used for
gt; gt; gt; what.
gt; gt; gt; It would be a lot easier if we could select the range of cells and then
gt; gt; just
gt; gt; gt; click on the appropriate colour in the quot;keyquot; section, and have that colour
gt; gt; gt; then applied to the selected range of cells.
gt; gt; gt;
gt; gt; gt; Is this possible ?
gt; gt;
gt; gt;
gt; gt;