Hi everyone,
I am working on a calendar project where my desired end result is to have a gantt style depiction of activities adjacent to employee names. A basic overview of the calendar is this:
$C$13:$NC$13 shows the month and year "mmm yy"
$C$14:$NC$14 shows the day of the month "dd"
$C$15:$NC$15 shows the day of the week "ddd"
$B$16:$B$390 is confined within an excel table (Table 5) and is under a header titled [Name], this is where I have a list of employee names.
Underneath the calendar there is another table (Table 2) where data is stored which depicts activities that I would like to display in the region of $C$16:$NC$390.
Each activity has an employee name, title, start date and end date. I have a formula that uses address, index and match to give me the cell address within $C$16:$NC$390 where the start date of an activity and the employee name intersect if it matches the information contained in Table 2. I duplicated the same formula to give an address of where the end date and the employee name match as well. Adjacent to those formulas I have a column where I concatenated the two previous values into a range.
I would like to conditionally format (color) the cells contained within $C$16:$NC$390 according to the ranges listed in the concatenate column of Table 2.
If possible I would like to show the name of the activity in the highlighted portion as well. I am at a loss as how to accomplish this task, most of the article I read show how to color cells based on their individual value, however in this instance I am relying on the value of different cell to color a defined blank range. I am more than willing to apply these outcomes through vba if necessary, I'm just trying to keep the file as small and quick as possible.
Any help would be greatly appreciated.
I am working on a calendar project where my desired end result is to have a gantt style depiction of activities adjacent to employee names. A basic overview of the calendar is this:
$C$13:$NC$13 shows the month and year "mmm yy"
$C$14:$NC$14 shows the day of the month "dd"
$C$15:$NC$15 shows the day of the week "ddd"
$B$16:$B$390 is confined within an excel table (Table 5) and is under a header titled [Name], this is where I have a list of employee names.
Underneath the calendar there is another table (Table 2) where data is stored which depicts activities that I would like to display in the region of $C$16:$NC$390.
Each activity has an employee name, title, start date and end date. I have a formula that uses address, index and match to give me the cell address within $C$16:$NC$390 where the start date of an activity and the employee name intersect if it matches the information contained in Table 2. I duplicated the same formula to give an address of where the end date and the employee name match as well. Adjacent to those formulas I have a column where I concatenated the two previous values into a range.
I would like to conditionally format (color) the cells contained within $C$16:$NC$390 according to the ranges listed in the concatenate column of Table 2.
If possible I would like to show the name of the activity in the highlighted portion as well. I am at a loss as how to accomplish this task, most of the article I read show how to color cells based on their individual value, however in this instance I am relying on the value of different cell to color a defined blank range. I am more than willing to apply these outcomes through vba if necessary, I'm just trying to keep the file as small and quick as possible.
Any help would be greatly appreciated.
