How to Highlight Text

clemkonan

Active Member
Joined
Jun 21, 2003
Messages
258
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

Active Member
Joined
Jun 21, 2003
Messages
258
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

Well-known Member
Joined
May 19, 2002
Messages
2,916
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top