System clock


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 



Hi sorry for the delay but been away for a few days. How do i incorporate the formula into a VBA macro as i run a vba macro every hour to update
Stuartc (rep: 2) Sep 26, '17 at 3:24 pm
I dislike shifting of goal posts which is why I don't do follow-ups. This question was answered. Someone here, or I, will answer your next question as well, if you care to formulate it as an independent task. However, if you want the final result more quickly I suggest that you spend some time to find the question whose answer will give you a solution.
Variatus (rep: 1840) Sep 26, '17 at 9:22 pm
Add to Discussion


Selected Answer

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.


Answer the Question

You must create an account to use the forum. Create an Account or Login