Conditional formatting and formulas not working

**mshain81** · 02-03-2010, 12:27 AM

Hello all, I'm new to the forums so I apologize in advance if this is not the right place to post this. I tried to locate the best area but am hopping this is it.

I have a date column and a text column. The SUSP has the formula =G1-450 in it which gives me the actual date in that column. The SUSP cells have Conditional Formatting set to turn red when its less than todays date.
I want to have it to where when I type Complete in the STATUS column, the actual date in the SUSP column turns back to black text color so only the incomplete shows the original Conditional Formating of red text. Is this possible? Is it a formula I cant figure out or is it something with the Conditional Formatting? I can import the spreadsheet if that will help. Let me know, I'll have to import it tomorrow when I get to work. Thank you in advance.

SUSP________STATUS
1 JAN 09______Complete

**ConneXionLost** · 02-03-2010, 12:38 AM

Hi mshain81,

Welcome to the forum!

All you'll need to do is modify your Conditional Formatting formula (probably with an AND function).

Post your workbook if you need further assistance.

Cheers,

**mshain81** · 02-03-2010, 11:15 AM

I'll be at work and post my spreadsheet in a few hours. In the mean time, anyone else?

**Huron** · 02-03-2010, 01:01 PM

Add another conditional formatting rule to have the font colour black if the Status cell contains Complete. Then in the conditional formatting rules manager move this rule above the date rule so that it gets applied first.

**mshain81** · 02-03-2010, 01:15 PM

Ok, I just uploaded my spreadsheet. Thanks for the replies so far but I still cant figure it out. Maybe a detailed explaination on what I need to do would help best. Thanks again.

**Huron** · 02-03-2010, 01:38 PM

Your profile shows Excel 2007 so the instructions I posted applied to that version. Your file is Excel 2003 so you have to delete the date CF and add the "Complete" CF first then the date CF as the second condition. See the attached file.

**mshain81** · 02-03-2010, 02:04 PM

Originally Posted by Huron

Your profile shows Excel 2007 so the instructions I posted applied to that version. Your file is Excel 2003 so you have to delete the date CF and add the "Complete" CF first then the date CF as the second condition. See the attached file.

Thank you so much. It worked like a charm. I figured it had to be something with CF and the formula option but I'm not that good with formulas.

Thanks to everyone else as well. Have a great day!

Mike