Population of cells with color based on value of other cells, skipping cells, etc.

Hello –

I work with process maps. I have created a spreadsheet with 3 columns named “Manual Task” “Walking” and “Waiting”. In each of these columns I enter the time it takes to complete a task in minutes. Example: 1.5

To the right of these three columns I have columns that represent 30 seconds of time. Right now I would manually color in these cells based on the length of the task. Example: 1.5 minutes is 3 cells.

If the next task takes two minutes, I skip over the first three cells that I filled in with color for task number one, and fill in the next four cells with color.

The effect looks quite a bit like a MS Project timeline.

Can you think of a way to automatically fill in multiple cells with color based on the value I place in another cell? Example .5= 1 cell, 1.0=2 cells, 1.5= 3 cells, etc.
Is there a way to then to skip over cells populated by a previous row, and fill the next cells with color?

Any help would be greatly appreciated.

Hi,

Not sure if this will help but try this example:

Select the range B1:K1, next go to Format/Conditional formatting, select "Formula is:" from the drop down menu and type:

=COLUMN(A1)*30/24/60/60<=$A1/24/60

next choose a format say red background and click Ok OK, now go to cell A1 and type a number between .5 and 5, the formatted cells should highlight in 30 seconds increments based on the number in A1, so if you type 1.5 in A1 cells B1, C1 and D1 should be highlighted.


HTH
Jean-Guy