How do I highlight a row in Excel based on the current time?

  • Thread starter Thread starter Mistress
  • Start date Start date
M

Mistress

I am building a multi-day TV schedule. Half-hours are listed in column A. I
would like to background-fill (highlight) the row corresponding to the
current half hour.
 
Mistress,

Use Conditional formatting, with the formula is option, and a formula like this:

=AND($A2<=NOW(),($A2+(0.5/24))>=NOW())

This assumes that the times in A are actually date and times: 12/3/2008 9:00:00 AM

Select all the cells that you want highlighted, with A2 as the active cell, and apply the CF. If
the active cell is somthing other than A2, then update the formula to reflect that. Select your
color, and it will show the current half hour.

HTH,
Bernie
MS Excel MVP
 
To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=AND(MOD(NOW(),1)>=$A1,MOD(NOW(),1)<$A2)
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=AND(MOD(NOW(),1)>=$A1,MOD(NOW(),1)<$A2)
5. Click the Format button and choose a format.
6. Click OK twice

If this helps, please click the Yes button.

Cheers,
Shane DevenshireHi,

The answer depends on how you entered the times - strictly as times or as
date/time. Let's assume times
 
Back
Top