Conditional formatting based on text in cell and a corresponding date

**livinglegend629** · 10-11-2010, 05:39 PM

I had asked another question before based on this same file so some of you might recognize this.

We have certificates that need to be renewed annually. We keep track of when the last renewal date was for each customer on an excel file.

I have a worker that references these certificates in another file. The customers name is inputed in a cell on this seperate file and the worker would like for the fill color to change if the certficate needs to be renewed in 90 days and also change depending on if it is also expired.

I think this can be done by combining conditional formatting with vlookup but I'm not completely sure how to go about doing this. I tried looking around through conditional formatting threads to look for a similar problem but I didnt find anythign quite the same. I apologize if this is a repeat thread, please point me to that thread. I can make a dummy workbook if need be, please let me know. Thank you guys (and gals) for your help so far.

**Leith Ross** · 10-11-2010, 06:41 PM

Hello livinglegend629,

If you can post a workbook it would be a big help. Having the workbook would answers lots of questions and provide you with an accurate solution.

**livinglegend629** · 10-12-2010, 11:29 AM

I've attached a dummy workbook; technically there are 2 workbooks being used.

The tab named File 1 represents the file that the worker would use, as we do transactions with a customer, this workbook is updated with the date, customer and certificate #.

File 2 represents the file that is updated with the customer name and certificate information. What i want to accomplish is that when our employee inputs a customer name and cert. #, the fill color of the cell would change color indicating the certificate status.

**livinglegend629** · 10-13-2010, 04:38 PM

Bump (do people do this on this site?)

**livinglegend629** · 11-11-2010, 05:10 PM

Can anyone help me with a solution to this problem?
Still have been trying to find a way to do this but no luck. I tried using vlookup to look up the expiration date of the inputed certificate number but the problem is that I don't know how to tie in ANY inputed value to vlookup since vlookup needs a specific value to look for, i.e.

=vlookup(VALUE, table_array, index_number, not_exact_match )

I tried entering the same cell into the value slot but then it said that it was a circular reference.

My next thought was that I could use if and then statements to change the background color based on what was entered into a cell by a user and check the expiration date which was based on something like this

Please Login or Register  to view this content.

Except I wanted to substitute the If statements with something like this,

Please Login or Register  to view this content.

And have one like that for each certificate

I know the structure is incorrect but I think i'm on the right track.

Any help would be greatly appreciated!