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


"Ben" wrote in message
...
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+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

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?


"Chip Pearson" wrote:

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


"Ben" wrote in message
...
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+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))

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 D5:D45 then your
formulas will look like this...
=LEFT(D5,5)="Early"
=LEFT(D5,4)="Late"

"Ben" wrote:

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?


"Chip Pearson" wrote:

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


"Ben" wrote in message
...
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+K5N5+T5,"Early by
"&TEXT(J5+K5-(N5+T5),"d""days & ""hh:mm"),"Late by "&
TEXT(N5+T5-(J5+K5),"d""days & ""hh:mm")))