Changing Text Format with Formula or Conditional Formatting with multiple values

[gerodr]

Hello all!

To give you an idea of what I am trying to do, let me break down what I have...

I currently have a workbook with multiple spreadsheets used to track requests received, and follow them throughout their life... sort of a "help desk" type of tracker, where we assign a ticket number and enter all the request info.

The spreadsheet has a few different "request types" that the user selects when creating a new line. The types of requests are located on column "D", and these are:

Invoice Submission
Payment Processing
General Inquiry

Each one of these requests has an estimated processing time

Invoice Submission - 10 Days
Payment Processing - 3 Days
General Inquiry - 1 Day

Now, this is what I am trying to achieve.
Column "C" has the date in which the request was received. What I would like to do is to enter a formula to change the color of the text in the corresponding cell, depending on the value. An example, referring to an entry Row 3:

IF the request type (D3) equals "Invoice Submission" AND the date (C3) is GREATER than 10 days from TODAY, change the text color to RED

So, if the request type is Invoice Submission and it has been more than 10 days from the date entered, change the Date color to RED.

If the request type is Payment Processing and it has been more than 3 days, make date RED.

If the request type is General Inquiry and it has been more than 1 day, make date RED.



What I've tried so far
I've tried using Conditional Formatting.
I've been able to use it to change the colors depending on the date calculation, using this formula

=(TODAY()-C3)>10

Using this formula in conditional formatting, I am able to change the color of all cells in which the formula is true. However, this formats all days which are greater than 10 days from today's date. As I mentioned before, different requests have different processing times.

I've tried using "AND" arguments, and I haven't been lucky so far.

I also tried using a complete "IF" argument in conditional formatting, but I believe it doesn't work, since conditional formatting is like an "IF" formula wizard on its own.


I am not yet hands and feet into VB, so I might need some help if this is needed.

Any help is truly appreciated!

Thanks,
Gonzalo <!-- / message --><!-- attachments -->

 

[Andrew Poulsom]

Welcome to MrExcel.

Make a list like this:

<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=194 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=130 height=17>General Inquiry</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Invoice Submission</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Payment Processing</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR></TBODY></TABLE>

and name it Table using Insert|Name|Define. Then try the conditional formatting formula:

=TODAY()>(C3+LOOKUP(D3,Table))

If you add to the table make sure that it is sorted ascending by the firat column.

 

[gerodr]

Andrew, that worked PERFECTLY!

Thank you very much for your fast response, and thanks for the welcome!
I hope to be able to learn and contribute to this forum.

Gonzalo