Conditional format a cell in time increments

  • Thread starter Thread starter Bojames
  • Start date Start date
B

Bojames

I have a macro creating a time stamp to a cell "d" once the first cell "c" is
changed. How do I make the cell turn yellow after hours orange after 16 hours
and red after 24 hours?
 
With the cell selected, click on Format | Conditional Formatting and
choose Formula Is rather than Cell Value Is in the first box. Put this
formula in the next box:

=cell_ref>=1

Then click the Format button, Patterns tab and choose Red, then click
OK. Click Add to set up your next condition, and choose Formula Is and
put this formula in:

=cell_ref>=16/24

Click the Format button and choose Orange on the Patterns tab, then
OK. Click Add once more, choose Formula Is and enter this formula:

=cell_ref>=8/24

then Format button again and choose Yellow, then OK twice to exit the
dialogue box. cell_ref is the cell you have selected.

You should note that Excel stores times internally as fractions of a
24-hour day, hence the values 1, 16/24 and 8/24 above. I'm guessing 8
hours for the last one, as you missed this out in your post.

Hope this helps.

Pete
 
Back
Top