time sum formula using one cell and respective rows from one column and conditional formatting

[PerryK]

I have a running timer in cell E3. In column Q starting at row 5 and going down, I want to have a difference equation of the running time and the time as input into column O via a clickable cell. This essential will give it a look of a stop watch. The equation for one cell is simple enough =sum(E3-O5). What I am asking is how can I copy this formula through the entire column Q? If I drag the box in the right corner it changes E3 to each respective E cell below (i.e. =sum(E4-O6). is there a way to not have to manually go to 1000 cells and manually input E3 in the formula? Is there a macro I can create to do this using the VBE?

Also I am trying to do some conditional formatting in column Q as well and can't get it to work. I want the cell to be green from 0 to 45 mins. Yellow from 45 to 90 mins and red from 90 mins on. I have the cells formatted in 13:30:00 (hh:nn:ss), I have tried a few different formulas I've found on line, but haven't had any luck yet.

Thank you in advance

 

[wideboydixon]

To anchor the E3 cell, use dollars like this:

=SUM($E$3-O5)

Then you can copy that down the sheet. For the conditional formatting, use formulas like this:

=Q5<=TIME(0,45,0)

Make sure you use "Stop if true" before adding the other ones for:

=Q5<=TIME(0,90,0)
=Q5>TIME(0,90,0)

WBD

 

[PerryK]

Thank you very much, that all works perfectly.