Conditional formatting for a date range

--------------------------------------------------------------------------------

Is someone able to assist.

I have a spreadsheet that lists the staff and which task they are t
perform on a given day. (Spreadsheet Name is Allocations)

Column A has the dates for a whole year starting at row 5

Row 4, Columns B to AD list the Staff Names

Row 5 onwards for Column B to AD lists the tasks. This way I can find
date in Column A, look across that row for a task, and see who is t
perform it.

Example:

Date Staff A Staff B Staff C
1/1/04 Zaa Zxy Zbg
2/1/04 Zxy Zbg Zaa
3/1/04 Zbg Zaa Zxy
4/1/04 Zaa Zxy Zbg
5/1/04 Zxy Zbg Zaa



The leave details are on another worksheet in 3 columns. (Spreadshee
Name is Leave)

These columns a

A. Name
B. Start Date
C. Finish Date

Example:

Name StartDate Finish Date
StaffA 1/1/04 2/1/04
StaffB 3/1/04 5/1/04
StaffC 2/1/04 4/1/04
StaffA 4/1/04 5/1/04

I need some code to colour the cells for a staff members allocation
when they are on leave.

Colour used is "VIOLET"

As can be seen by STAFFA, above, sometimes staff have 2 or more leav
periods in a year. eg 2 weeks in March, 2 weeks in October

The code therefore needs to:

1. Look at staff Name on Leave Sheet.
2. Find corresponding Name in Row 4 on Allocation Sheet.
3. Look at Start Date and Finish Date on Leave Sheet.
4. Colour that range for that staff member on Allocation Sheet.

With the above example the following cells marked X would be shade
Violet.

Date Staff A Staff B Staff C
1/1/04 X - -
2/1/04 X - X
3/1/04 - X X
4/1/04 X X X
5/1/04 X X -


Variables:
Sometimes there are more or less Staff Members.

Staff may have 2 or more leave periods.


I have already used the three Conditional Formatting options t
highlight various tasks, so need code to shade the cells

I have very limited knowledge of macros and code, and dont know if thi
is possible.

Could someone please assist.

cheers
Koal

--
Message posted from http://www.ExcelForum.com

Hi Koala,

Here is some code. Note that is event code so it will set the colour as you
enter the tasks. To update what you already have you need to edit each cell
(just select the cell, F2, Enter).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRows As Long
Dim i As Long
Dim shLeave As Worksheet
Dim Matched As Boolean

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Row 4 Then
Set shLeave = Worksheets("Leave")
cRows = shLeave.Cells(Rows.Count, "A").End(xlUp).Row
Matched = False

For i = 2 To cRows
If shLeave.Cells(i, "A").Value = .Parent.Cells(4,
..Column).Value Then
If shLeave.Cells(i, "B").Value <= .Parent.Cells(.Row,
1).Value Then
If shLeave.Cells(i, "C").Value =
..Parent.Cells(.Row, 1).Value Then
Matched = True
Exit For
End If
End If
End If
Next i

If Matched Then
.Interior.ColorIndex = 13
End If

End If

End With

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
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"koala " wrote in message
...
Conditional formatting for a date range

--------------------------------------------------------------------------
------

Is someone able to assist.

I have a spreadsheet that lists the staff and which task they are to
perform on a given day. (Spreadsheet Name is Allocations)

Column A has the dates for a whole year starting at row 5

Row 4, Columns B to AD list the Staff Names

Row 5 onwards for Column B to AD lists the tasks. This way I can find a
date in Column A, look across that row for a task, and see who is to
perform it.

Example:

Date Staff A Staff B Staff C
1/1/04 Zaa Zxy Zbg
2/1/04 Zxy Zbg Zaa
3/1/04 Zbg Zaa Zxy
4/1/04 Zaa Zxy Zbg
5/1/04 Zxy Zbg Zaa



The leave details are on another worksheet in 3 columns. (Spreadsheet
Name is Leave)

These columns a

A. Name
B. Start Date
C. Finish Date

Example:

Name StartDate Finish Date
StaffA 1/1/04 2/1/04
StaffB 3/1/04 5/1/04
StaffC 2/1/04 4/1/04
StaffA 4/1/04 5/1/04

I need some code to colour the cells for a staff members allocations
when they are on leave.

Colour used is "VIOLET"

As can be seen by STAFFA, above, sometimes staff have 2 or more leave
periods in a year. eg 2 weeks in March, 2 weeks in October

The code therefore needs to:

1. Look at staff Name on Leave Sheet.
2. Find corresponding Name in Row 4 on Allocation Sheet.
3. Look at Start Date and Finish Date on Leave Sheet.
4. Colour that range for that staff member on Allocation Sheet.

With the above example the following cells marked X would be shaded
Violet.

Date Staff A Staff B Staff C
1/1/04 X - -
2/1/04 X - X
3/1/04 - X X
4/1/04 X X X
5/1/04 X X -


Variables:
Sometimes there are more or less Staff Members.

Staff may have 2 or more leave periods.


I have already used the three Conditional Formatting options to
highlight various tasks, so need code to shade the cells

I have very limited knowledge of macros and code, and dont know if this
is possible.

Could someone please assist.

cheers
Koala


---
Message posted from http://www.ExcelForum.com/