Conditional Formatting on Time

S

Scott

Hopefully im in the right section for this question.

Is it possible to base a conditional format on the =Now()
expression? Basicly if the time is before or after the
time, I want the conditional formatting to return a
specific color. Ive tried =A1>NOW() and set A1 to display
time, but I cant seem to get it to work. Thanks for the
help.
 
M

Michael Malinsky

I put a time value in A1 then selected A2, went into conditional formatting
and selected Formula Is and entered:

=$A$1<NOW()

and set the cell background to change to red if the condition is true. Cell
A2 turned red as expected.

I also created a conditional format in A1 for the same condition. In
conditional formatting, I selected Cell Value Is, selected less than, then
inserted the following:

="NOW()"

and set the background to change to red. This turned A1 red as expected.

HTH
Mike
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
G

Guest

Sorry, I just read my post and it might be confusing, hope
this makes more sense.

if the time in A1 is before or after the current time, I
want the conditional formatting to return a specific color.
Ive tried =A1>Now() and =A1<Now() but cant seem to get it
to work. In A1, Ive set the number category to time and
the type is h:mm AM/PM.
 
D

David McRitchie

Hi Scott,
Actually you should be in Worksheet.Functions since this is
builtin Excel worksheet functions.

I'm not sure what you want even after rewording, but it seems like
if the date in Column A is the same as the current date then the
cell is to remain uncolored; otherwise color the cell.

Formula 1 is: =OR($A1 < TODAY(), $A1> TODAY() )
or
Formula 1 is: =$A1<>TODAY()


If you want three different colors:
Formula 1 is: =$A1 < TODAY()
Formula 2 is: =$A1 = TODAY()
Formula 3 is: =$A1 > TODAY()

The formula used is based on the active cell.

By using $A1 in the formula you can select
the single cell A1 for coloring
or by selecting Column A, each cell in A is tested and colored if needed.

or by selecting the entire sheet with cell A1 as the active cell, each row
will be colored depending on the value in column A of each row.

I have a page on Conditional Formatting:
http://www.mvps.org/dmcritchie/excel/condfmt.htm

This was a test on date if you really mean time as in MOD(NOW(),1)
then just a little more information, because the window of not being smaller
or larger is going to be VERY small.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Top