Conditional format issue

[Mike Busch]

In 2003 when I try to use a conditional format on a date field, I select the
field and set if for less than Now (). This will change the date to red when
a bill is past due. I am doing something wrong, because no matter what date
is there, all the date cells in this field turn red. What am I doing wrong.
Thanks in advance

 

[Ron Rosenfeld]

In 2003 when I try to use a conditional format on a date field, I select the
field and set if for less than Now (). This will change the date to red when
a bill is past due. I am doing something wrong, because no matter what date
is there, all the date cells in this field turn red. What am I doing wrong.
Thanks in advance

Take a look at the conditional format formula.

If you just type <Now() into the formula box, Excel may have turned it into a
string: ="<Now()"

You may just need to delete the quote marks, if that is the case.
--ron

 

[Chip Pearson]

In the Conditional Formatting dialog, change "Cell Value Is" to
"Formula Is" and enter

=A1<NOW()

where A1 is the cell or first of the cells to which CF should be
applied. If you don't use the "Formula Is" option rather than "Cell
Value Is", the CF won't work properly.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Shane Devenshire]

Hi,

To conditionally format your cell(s), lets assume A1:A100:

In 2003:
1. Select the cell 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:
=A1>TODAY()
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:
=A1>TODAY()
5. Click the Format button and choose a format.
6. Click OK twice

Its OK to use NOW() but I choose TODAY()

If this helps, please click the Yes button.

Cheers,
Shane Devenshire