Changing Font Colour

[mlv]

Is there any way of changing the colour of the text in a cell when using an
'IF' statement?

I have a formula that looks at the status of a cell and then puts the
appropriate text into another cell.

I would like this text to be normally black (which it is by default), but
red if the IF statement is not satisfied.

Is this possible? Is there some code that can be used to change the font
colour?

Thanks

 

[Sandy Mann]

Check out Formatting > Conditional formatting to do what you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

See help on Conditional Formatting.


Gord Dibben MS Excel MVP

 

[Bob I]

Is there any way of changing the colour of the text in a cell when using an
'IF' statement?

I have a formula that looks at the status of a cell and then puts the
appropriate text into another cell.

I would like this text to be normally black (which it is by default), but
red if the IF statement is not satisfied.

Is this possible? Is there some code that can be used to change the font
colour?

Thanks

 

[mlv]

Use Conditional Formatting.

I've read the help files on Conditional Formatting, but I think I need
spoon-feeding on this one...

I can get Conditional Formatting to work fine on cells with a fixed value,
but I can't get it to work on the cell with the variable text that I want to
change to red.

Simple example - formula in cell:

=IF(Logical_Test,"Value is within set limits","Warning - value is outside of
set limits")

Normally the text colour is default black. However, if the cell shows the
text "Warning - value is outside of set limits", I would like that text to
be red.

I guess I really only need to search for the keyword "Warning" within the
cell text. I assume I need to use 'Formula is' rather than 'Cell Value is'
in the Conditional Formatting dialog box?

Can anyone help with the formula?

Thanks

 

[Bob I]

Cell Value is Equal to
="Warning - value is outside of set limits"

Pick pattern and the color red

 

[mlv]

Cell Value is Equal to
="Warning - value is outside of set limits"

Thanks Bob.

Interestingly, that was the first thing I tried, but it didn't work for me.
It seems some pilot error crept in.

I didn't type the leading '=' sign because the Conditional Formatting
dialogue box seemed to put that in automatically. I just entered the text
in quotation marks.

I've now spotted that not only is the '=' sign put in automatically, but so
are the quotation marks, so my entered text actually ended up as:

=""Warning - value is outside of set limits""

Which is why it didn't work.

It works now

Another thing...

The actual 'Equal to' text line I have to match is much longer than the
example I gave in this post. It exceeds the width of the text box field,
which is why I was hoping I could use a much shorter keyword search instead.

I can't find a way of navigating through the text line to check for errors.
Using the mouse cursor only works on text that is visible within the text
box field. Using the arrow keys to scroll to text that extends beyond the
text box field doesn't work (for me). Instead the arrow key inserts an
adjacent cell reference (such as +$B$52) into the text line.

So, what am I missing? How do I scroll right to bring hidden text into
view? I'm using Excel 2002 SP3.

I have just discovered that clicking on the icon to the right of the text
field extends the field to the full width of the dialogue box, but the
scroll problem persists if the text line is still longer than the extended
text box field.

Thanks

 

[Bob I]

If all you want to pick off, is a part of it, here is an example using
your example placed in B1 and checking the value in A1. CF on B1 is

Formula is =MID(B1,1,7)="Warning"

and set it to red

 

[mlv]

If all you want to pick off, is a part of it, here is an example
using your example placed in B1 and checking the value
in A1. CF on B1 is

Formula is =MID(B1,1,7)="Warning"

and set it to red

Thanks Bob

 

[Bob I]

Thanks Bob

You're welcome, have a good day!