Change cells colour with elapsed time, Conditional Formatting?

[Aerotech]

Hi, I wonder if anyone might be able to help me.
I need to be able to change a cell colour in relation to elapsed time, i.e. “Green” for 00:00 Hours and progress through “Amber” and finally “Red” for when the allotted time is reached, the times will vary from between 1 Hour and 16 Hours.

I have a vba code running which automatically puts the date in Cell F3 and the time in Cell G3 when cell F3 is selected, or “Clicked”on, This is the start time.

In Cell H3 I need to be able to enter a simple 1 for 1 Hours, 2 for 2 Hours etc. It doesn’t really matter if this is shown as a time, as long as in cell I3 it can be interpreted as the finish time,ie the time in cell G3 plus the value entered in cell H3, such as 16:00 + 1 = 17:00.

The vba code also automatically puts the date in cell K3 and the time in cell L3 when cell K3 is “Clicked”, this is the finish time.

Cell J3 is the “elapsed time” and is derived from a macro running a clock, it starts by taking the current time and subtracting the time from cell G3.

I must point out that I have most of this already running, with a few exceptions, I can’t get the “Elapsed Time” cell to change colour as the times go from start to finish using conditional formatting.

I would also like to be able to stop the elapsed time from counting when cell K3 is selected for the finish time, and the duration from start to finish put in cell M3.

I have quoted row 3 throughout this request as the project is experimental at the moment and this is the range of cells I have been using, Once up and running there will be hundreds of rows used.

Thanks In Advance.

 

[sijpie]

For starters, if you think that after you have entered your starting conditions, you can then watch the colours in the cells slowly change, that most likely won't work. Well it can, but then the spreadsheet is fultime busy and you will not be able to access it.

But if you want to see the status any time you click on say a status cell, that can be done.

Have you got the conditional formatting to work in so far that it shows colours progressing (albeit static) or can you not get the colours to come up at all? That is not clear from your story.

Excel 2010
	J	K	L	M	N	O
13	Start time	Current Time	Deadline	Elapsed	Max Duraton	Progress
14	01:35	17:11	18:46	15:36	18	87%
Sheet2

Cell Formulas
Range	Formula
K14	=NOW()
L14	=J14+K14
M14	=(K14-TRUNC(K14))-J14
O14	=1*M14*24/N14

And conditional formatting set on the Cell O14: Format cell on value / Data Bar / Type:Number from 0 to 1 / Gradient Fill

 

[Aerotech]

Hi
Thank you for your reply, and sorry for my late response, but we have been away for a week and only just arrived home.

To try and clear up what the problem is, I was hoping to use conditional formatting and use the cell reference A1 - B1 for example to change the colours, but I soon found out that would not work.

I can get the cell to change colour if I format the cell as a number and use the time entered in conditional formatting as say 0.0416666 (Start), 0.0625000 (Midpoint) and 0.0833333 (End), but this would have to be entered manually, I need to be able to get the times is automatically.

Hope this clears up things a little, and in the meantime (when we've finished unpacking) I will have a play around with the information you have kindly given above.

Regard's,
Gary.