Conditional Formatting with Time

[Colin]

In one row I have 24 cells, each cell representing 1 hour. I would like a
cell to change color, say from white to yellow, during the hour it
represents on the clock. I have tried conditional formatting with =HOUR,
Now() and =TIME without success.

I would greatly appreciate any assistance in solving this problem.

Colin

 

[Jacob Skaria]

1. Select the cell/Range (say A1:X1). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
'if values from A1 to X1 are entered as 1,2,3,....24 then
=A1=VALUE(TEXT(NOW(),"hh"))

OR if values are entered in time format 1:00 AM, 2:00 AM etc; then try
=TEXT(A1,"hh")=TEXT(NOW(),"hh")

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

If this post helps click Yes

 

[Colin]

Sorry for lack of detail. In my SS cells B17 to Y17 are number 0 to 23,
representing 24 hours. Directly below each row 17 cell is a blank/empty
cell. During the period midnight to 1am I would like only cell B18 to have
a yellow fill, then during the period 1am to 2 am I would like only cell C18
to have a yellow fill and so on for the full 24 hours after which the
colored cell returns to the beginning and starts the process again.

Colin

 

[Fred Smith]

Try a CF of:
=B$17=hour(now())

Regards,
Fred.

Sorry for lack of detail. In my SS cells B17 to Y17 are number 0 to 23,
representing 24 hours. Directly below each row 17 cell is a blank/empty
cell. During the period midnight to 1am I would like only cell B18 to
have a yellow fill, then during the period 1am to 2 am I would like only
cell C18 to have a yellow fill and so on for the full 24 hours after which
the colored cell returns to the beginning and starts the process again.

Colin

 

[Jacob Skaria]

1. Select the cell/Range (say B18:X18). Please note that the active cell in
the selection should be B18. Active cell will have a white background even
after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=B17=HOUR(NOW())

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes