Conditional formatting question

[jyakapucci]

I am trying to use conditional formatting in Excel 2003. Here is my
problem. I have a series of columns of dates that is use Conditional
Formatting to turn red, blue, or leave black based on how long ago the
date was. That part works fine. Here is what I would like to do. I
would like to set the color of the text in the first column, (in this
case, a person's name) to red if any of the dates in that row are red
and blue if any of the dates in the row are blue. In the case of both
red and blue dates, I would want the name to be in red. Thanks much
for any help.

 

[dgp]

You may be able to use a formula to define the condition for the names.
A formula condition allows you to reference other cells values to
determine with the condition is true of false.

Here's a simple example based on the data below.
I want the Name to be red if either date is less than 1/1/2006.

A B C
1 Name1 1/5/2006 1/5/2007
2 Name2 12/4/1985 12/4/2008

1) Select the cells containing the names, A1:A2

2) Determine which cell is active. Your condition will have to be
written relative to the active cell. I'll assume that Cell A1 = Name1
is active.

3) Select Format>Conditional Formatting from the menu.

4) Click the pull-down menu and select 'Formula Is'.

5) In the input box I'd enter my condition as a formula:
=OR($B1<38718,$C1<38718). 38718 is 1/1/2006 in numerical format. The $
in front of the B and C ensures that the formula will always reference
my date columns (important if you need to copy the format to other
columns).

You can also use >, <, <=, >=, <>, AND, & NOT for your condition
formulas.

I hope this helps. If you need more specific advice, please post the
conditions you are using to currently color the dates and I'll try to
help.

Dave Parker

 

[Jimmy Yakapucci]

This helps some. One problem is that with each name there are about 12
- 15 columns of dates associated with it and the formula could get very
long. Here is what I am doing with the dates: Using the current date,
if it is over 6 months ago, make the date blue, if it is over a year
ago, make the date red. That part works fine. I just need to somehow
make the name change color based on the color of the dates. It would
be nice if there was some way to make a test that states:

for the range B1:C1 if any date is blue, make the name blue
for the range B1:C1 if any date is red, make the name red

Doing it in that order would make sure that if there were both red and
blue dates in the row, the name would be red.

I don't have the exact test on hand since I am posting from home and
the code is at work. Also, this is a spreadsheet that I inherited, so
I am not the original author.

Any ideas? Thanks.

 

[dgp]

I think this is close. You'll probably want to refer the conditions
you're using on the date formatting to more accurately determine the 6
month and 12 month values - I've just used 6*31 days and 12*31 days.

Place the following conditional formatting on the names. The range
$B1:$C1 can be expanded to include as many dates as needed.
Condition 1, Formula Is, =OR($B1:$C1<TODAY()-12*31), Red
Condition 2, Formula Is, =OR($B1:$C1<TODAY()-6*31), Blue

Dave Parker

 

[Jimmy Yakapucci]

That just might work. I'll have to try it out when I get back to work
Sunday night and I'll let you know how it turns out. Thanks again.

 

[Jimmy Yakapucci]

That fixed the major prtion of my problem. The problem that I am
having now is that if any of the dates are empty, another test in the
conditional formatting changes the background color to yellow. Then it
doesn't change the text color. Will it only apply a single condition?
Thanks.

 

[dgp]

For Excel 2003 and earlier, only the first condition evaluated to be
TRUE is applied to the cell.

You could add another condition to the names that combines mulitple
conditions. To show red font and yellow background if date is more than
12 months from today AND one of the dates is blank:

Condition:
Formula Is, =AND(OR(B1:C1<TODAY()-12*31),OR(ISBLANK(B1:C1))), Red
Font/Yellow Background

This should be the first condition. If it is not true then the
remaining conditions will be evaluated.

Dave Parker
West Palm Beach, Florida

 

[Jimmy Yakapucci]

I'll give it a shot. The other problem that I am running into is that
since I have so many columns that I am running out of space for the
test.