How to Highlight Text

[clemkonan]

I want to track complaints using an Excel sheet , part of the design requires calculating the difference between two dates and based on the result rating the complaint status as "past due" or " coming due". "Past due " is a high risk situation and should be in red text, "coming due" should be in yellow text.

My columns with examples are:

Complaint # - 34567

Product Name - Danish Cookies
Concern - Illness
Date received - 01/12/04
Target Closure Date - 01/15/04
Todays date - 01/20/04
Days Elapsed - 8
Status - open
Comment - Past Due

Status has a value of "open" or "closed". If status = open an IF statement calculates the days between date received and todays date this is "days elapsed".
The "days elaped"column is then used to generate the text string " coming due" or "past due" based on if the value is > 0 or less than 0.

I think I can get the IF statement to work but I have no idea how to get the test to be coloured red or yellow.

 

[Juan Pablo González]

To format cells depending on certain conditions, you use the menu Format | Conditional Formatting..

For example, if you have a cell like

=A1*10

and you want to color it red when its less than 50, you select this cell, go to that menu, and select

"Cell is", "Less than", 50

and then, click on the pattern button and change the format as you want it to appear, and that's it !!

 

[clemkonan]

Thanks for the tip , I am not sure this would work in this case. Recall I am calculating the diffeence between two dates for records that are classed as open. If at D11:

Date1 - Date 2
Feb 15 Feb 1 = 14
Feb 15 Feb 12 =3
Feb 15 Feb 15 =0
Feb 15 Feb 29 = -14
I want a value of 14, 3 and 0 to be yellow meaning that the file is not past due, but if the file is still open on Feb 29, -14 will produce a red flag. same for -1, etc.
Can I use an IF statement in the conditional formatting you describe and if so can you kindly illustrate how?
Thanks

 

[Seti]

First, format the text as yellow so that all results will be yellow except when the conditional formatting kicks in and changes the text to red. Set the condition to:

Cell Value is < 0

then format as Red text.