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