Applying colour to my Formula

[Ben]

Hi
Someone on this site previous has make up this great formula for me (see
below)
What i need now is when the following answer is displayed the text colour
changes accordingly:
On Time (blue)
Late by(Red)
Early by(green)
I believe it's to do with conditional formating but just can't work it out.
Thanks

=IF(J5+K5=N5+T5,"On-Time",IF(J5+K5>N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

 

[Chip Pearson]

Yes, you can do this with Conditional Formatting. Choose
Conditional Formatting dialog from the Format menu, and in that
dialog, choose "Cell Value Is" and "Equal To" and enter 'On Time'
(no quotes). Then, click the Format button and choose your fill
color. Repeat these steps for your other conditions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Toppers]

Ben,

Select column to be formatted

Format==>Conditional Formatting

Cell Value Is ==>equal to ==>On Time

Click Format.... ==>Pattern==>Select colour (BLUE)

Click Add>> button

Repeat above for other 2 conditions

HTH

 

[Toppers]

Ben,
Ignore my last posting ..it won't work. I should have looked more
carefully at your formula.

 

[Ben]

Hi Chris

Almost there, i've done as you said but the colour change is only being
applied when the answer is On-Time.

This may be because when the answer is displayed for Early by or Late by, it
is followed by a time i.e. Early by 0days & 1:00,but i can't set this as
the conditional formula because the time can change from cell to cell.

Have you a fix for this?

 

[Ben]

Hi

It's works great when the answer is On-time,but not for when the answer is
Early by or Late by.

I think this is because these are then followed by a time i.e late by 0day &
1:00

Any ideas
Thanks
Ben

 

[Sloth]

In conditional format select "Formula is" instead of "Cell value is", and
insert these formulas for the second and third condition.

=LEFT(A1,5)="Early"
=LEFT(A1,4)="Late"

A1 is the cell in the top left of the highlighted area that you are applying
the conditional formatting to. For example, if you select D545 then your
formulas will look like this...
=LEFT(D5,5)="Early"
=LEFT(D5,4)="Late"

 

[Toppers]

Ben,
Use "Formula is" and enter =Find("Late by",C1) ... assuming C
is the column for CF and colour as before. Repeat for On Time and Early by.
(You can just test For Early/Late, omitting the "by").

HTH