Conditional Formatting - Multiple Criterias

[Tom]

Hi all,

I've tried a couple of the other examples for Conditional Formatting with
multiple criterias, that have been discussed on here, but I have been unable
to get it to work.

I have a spreadsheet with 7 different milestones, which tracks milestone
dates for 112 projects. Each milestone has a column of forecast date and a
column of an actual date. For example:
Milestone 1:
Forecast dates in C2 to C113. Actual dates in D2 to D113
Milestone 2:
Forecast dates in E2 to E113. Actual dates in F2 to F113
etc.

If a milestone has not been reached, the cell is blank.

What I'd like to have happen is that when a forecast date is today or older,
while the actual date is blank, I want the forecast date to be formatted to
have a red font.

I'm pretty novice when it comes to Excel, so what I've been trying is
variations of
=AND(D2="",C2<TODAY()), but it doesn't seem to work.
Would it be best to format on a per column basis, or is it possible to do
one for the entire spreadsheet?

I'm using Excel 2007.

Anyone that can help? Let me know if you need additional information.

Thanks,
Tom

 

[Tom]

Looks like I was able to do it to one single cell using:

=AND(D19="",C19<U1)

where U1 is =NOW()

Anyone know how I would go about getting this to more than just one cell,
without going in to each individual cell to create a rule?

Thanks,
Tom

 

[AltaEgo]

=AND(C2<TODAY(),ISBLANK(D2))

 

[Tom]

Thanks Steve, but that doesn't seem to work..

I got it to work in a single cell using
=AND(D19="",C19<U1)

where U1 is =NOW()

So now I'm wondering how I can apply this to several columns, without going
to each cell and setting up a custom conditional formatting for each of them.

Tom

 

[Pete_UK]

Which cell is this being applied to? C19 ?

Do you need to refer to NOW in some other formula? If not, then you
could include it within the CF formula, and as you are not interested
in times, then you could use TODAY instead.

Highlight all the cells within the column that you want to change, eg
C2:C113,making sure that C2 is the active cell, and use this CF
formula for the first condition:

=AND(D2="",C2<TODAY())

Set your format and click OK twice to exit the CF dialogue boxes. That
condition will now apply to all the cells that were highlighted. Use
the Format Painter icon to apply the same CF conditions to columns E,
G, H etc.

Hope this helps.

Pete

 

[Tom]

Thanks Pete, that did it! I appreciate your help!

 

[Pete_UK]

You're welcome, Tom - thanks for feeding back.

Pete