Hello, I have been working on a task for a long time and I'm very close to a solution. I am populating a resourcing spreadsheet with the conditional format:
To give background on the project the format above performs the task that if it reads an X in the 'Holidays' sheet on a day for a resource then it will place an X in the cell. Otherwise it will sum the amount of hours of a range (one day) in the 'Projects' sheet for that specific resource in the 'People' sheet. The format then extends from the name of the resource ($B4) for multiple weeks depicting the amount of hours for each day.
The next task required the ability to add and remove resources within a list of multiple resources while allowing the conditional formatting to remain modular.
The above code allows to copy in a resource and then change the name with the conditional formatting performing as required. However the range of 'Holiday' does not behave as hoped while the range of 'People' does. For Example: The following lines of formatting show the rows at 4 (same row where the conditional format above is found), 5, 6 and 7 when the VBA code above is ran with i = 4.
As you can see the range for People updates as I'd hope but it does not for Holiday.
I have tried a number of different solutions but this one I have found to get me the closest to the finish but just short. If I cannot get this to work I will implement all the cells with just the SUMIF so the conditional format updates with the VBA code and use more VBA code to add in Holidays manually however this will be very inefficient. I am very open to any suggestions on how I may get this to work and thanks in advanced for any time considering this problem of mine! Happy to attach the workbook if anyone would like a closer look.
Bookmarks