How do I use the system clock to condition format a selected cell. I.e. between 6 and 7 am if value in cell is between 0 and 9 then cell goes red. Between 10 and 30 then amber. A above 30 then green

# System clock

### Discussion

## Answers

The basic formula looks like this:-

`=AND(MOD(NOW(),1)>0.375, MOD(NOW(),1)<0.5,A1>0, A1<5)`

0.375 is 9 AM and 0.5 is 12 noon. Calculate these numbers by dividing the time, expressed in 24H system into 24. 6AM = 6/24 = 0.25, 6 PM = 18/24 = 0.75. 10 AM = 0.4166666667 which implies a precise time down to a thousandth of a second. For most practical purposes you can round to 3 or 4 digits.

You might write the cell reference in relative terms (as above) or absolute (like $A$1) or mixed, depending upon how you intend to copy the cell format around your sheet.

You can make several rules applicable to the same cell(s). For your requirement you should have different formulas, all based on the same pattern, for each colour.

Remember that the defined cell colouring will be applied if the formula evaluates to TRUE. At other times the "natural" fill colour of the cell will be shown. In your case the "natural" colour should probably be green. So you don't need to think about green.

Finally, please also bear in mind that the cell colour can only change when the worksheet is recalculated. Calculation takes place when you load the worksheet or when changes occur. If you wish for the colours to change just as time passes you would need VBA to induce a recalculation at specific times. However, once you opt for VBA I would recommend to let VBA do the colouring and dispense with conditional formatting altogether.