ExcelBanter - Conditional formatting date ranges

hey daddylonglegs, the first two conditions work great! its the 3rd
condition.... it wants to highlight the whole calendar even if there is no
3rd employee for the vacation, also only certain rows contain the dates but i
noticed if you only select the rows the condition dont work but selecting the
whole area it works fine.

i found the source code... maybe that will help with the visuals a little
better
http://support.microsoft.com/default...&Product=xlw2K

thanks in advance!
"daddylonglegs" wrote:

Do the cells A1:G12 contain actual dates?

If so you could do the following:

Select A1:G12 and apply the following conditions with "formula is" option

condition 1
=SUM((A1=$B$15:$B$25)*(A1<=$C$15:$C$25))=1
format as desired, e.g. green

condition 2
=SUM((A1=$B$15:$B$25)*(A1<=$C$15:$C$25))=2
format as desired, e.g. amber

condition 3
=SUM((A1=$B$15:$B$25)*(A1<=$C$15:$C$25))2
format as desired, e.g. red

This will give you one colour for days with a single person on vacation,
another colour for two people on vacation and a third colour for 3 or more on
vacation. Of course it also formats weekends but that can be altered if you
wish.....

"arkmpr" wrote:

A1:G12 is a calendar (VBA code from microsoft) / A15:A25 is name field /
B15:B25 is start date field / C15:C25 is End Date field.

1. I want the dates between start/end to highlight if an employee wants
those as vacation days. (i can figure this out if only one employee takes a
vacation but not for several employees within the same month ie max 3
conditional formats
2. Can i have multiple highlights if employee vacations have crossover dates
for ex: mike jan 2 - jan 5 and todd jan 4 - jan 8.

if you think something else would benefit me more pls let me know
thanks in advance