-1

Similar to a Gantt Chart but not quite the same, I'm trying to color a variable range of cells on the same row.

As you can see I have a variable number of tasks and a variable start and end for each task

I've created a simple time series where the "Machine 1", which is in the row number 6, is making all the tasks. The time series is in the row number 3.

So basically, I need to color the ranges given by the tasks table in the Machine 1 row, using that time series as a reference to start coloring the cells. Each task has to have a different color.

As I'm a beginner in VBA, I've been trying to do this using a formula:

=IF(AND($C4<=AK$3;$D4>=AK$3);1;0)

Being C4 the start of the task, AK3 the place of the time series right now, and D4 the end of the task. Then I would fill the whole Machine 1 row. This would give a 1 in the range of the task and a 0 before and after the task, then I could format the row and color the cell by the given value in each cell. (1 color and 0 blank)

The problem is that this only works for one task and I really don't know how to change the formula to add the other tasks. I'm pretty sure this can be done in VBA but, like I said, I'm still a beginner. Please help me

The final answer should look like this. The color doesn't matter, it's how to color the variable ranges automatically the problem

A. Rojas
  • 3
  • 2
  • The relationship between Machine1 and tasks are not clear. How will we put tasks into gantt chart, could you revise your 2nd screenshot to show this? – Hakan ERDOGAN May 23 '18 at 17:54
  • I've updated the question, I added what the final answer should look like. The Machine 1 makes all the task, so all of the tasks should be in a gantt like type chart where you can see the tasks that the Machine 1 will be making, in this case "a", "b", and "c". – A. Rojas May 23 '18 at 20:14

1 Answers1

0

If I understand what you want correctly, this can be done with conditional formatting:

Use four conditions, one for each of the following formulas:

=COLUMN(AK6)<=$D$7+37
=COLUMN(AK6)<=$D$6+37
=COLUMN(AK6)<=$D$5+37
=COLUMN(AK6)<=$D$4+37

and apply them in that order to $AK$6:$BZ$6

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
  • I had to modify the conditions a bit so that way there could be blank cells if the range said so, but the logic was just what I needed. Thank you very much! – A. Rojas May 24 '18 at 07:08