Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations SDETERS on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - Change Text Color Within “IF” Formula 1

Status
Not open for further replies.

BSVBD

Structural
Jul 23, 2015
463
Excel Spreadsheet
Microsoft Office Standard 2010

Within the following formula, I want the red portion to appear red if the applicable conditions apply.

=IF(F35<F24,(IF(F34<F24,"Plate must be wider than beam flange >>>","Actual / Specified Plate Width =")),"Actual / Specified Plate Width")

When inside of the editing mode, when I highlight the applicable text, the font editing options fade making it unable to edit or change the color.

How can I do this?

Suggestions? Comments?

Thank you!
 
Replies continue below

Recommended for you

Put the following formula in a different cell, let's assume cell A1, to determine the need for the color change:
=IF(F35<F24,IF(F34<F24,1,0),0)
Apply conditional formatting on the cell where the text needs to change color (the one with your above formula). Create a new rule: Use a formula to determine which cells to format, in the "Format values where this formula is true" box, select A1 (it will enter $A$1, but that's fine). Click Format, then select the color red for the font color and click OK.

Under that condition, the text will be red; under all others it will be black.

xnuke
"Live and act within the limit of your knowledge and keep expanding it to the limit of your life." Ayn Rand, Atlas Shrugged.
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
 
You need to look into using conditional formatting to achieve this type of formatting. It should be fairly self explanatory using either cell value equals or a formula based on the inequality you have in the if statement, but post back if you cannot achieve what you're after.

You cannot change text colour using an IF statement like you're trying to do.
 
Pretty straightforward with conditional formatting; I chopped out some rows and columns for simplicity
red_bbgphl.gif

red2_hojexy.gif

red3_vzdrgi.gif


TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
Got it!

Success!

Just gotta know how!

Thank you all!
 
Nice, IR! Even simpler than mine.

xnuke
"Live and act within the limit of your knowledge and keep expanding it to the limit of your life." Ayn Rand, Atlas Shrugged.
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
 
IRS: Is that ever useful...

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Feel any better?

-Dik
 
We use that sort of formatting, along with dark green on light green background all the time for test data sheets to show pass/fail; unfortunately, we often have more fails than passes, which is not a good look [curse]

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
Dik, I find visual "colour" cues are much better than just writing some text on a sheet regarding a failure that may not grab the users attention.

Looks bad in calculations if bad red cells are still present, designer or checker cannot really ignore the error inadvertently. Designer needs to alter design until all aspects pass for example, much harder to miss.
 
concur... highlights things to check to see if they are close enough to accept....[lol]

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Feel any better?

-Dik
 
I almost always use
Use a formula to determine which cell to format
in my CR Rule Type.

In this case the formula I'd use is
[tt]
=AND(F35<F24,F34<F24)
[/tt]
...so when that expression is TRUE, the CF is applied. These are the values that the IF is using.

But I absolutely hate cell notation! I would be using Named Range names in all my references. Makes understanding what's going on much easier!

PS: something else to consider. What if the boss wants the wording changed to exclude the word "must"? The criteria may be met in cells F34, F35, F24, but the CR criteria is not connected to the main criteria, but some other arbitrary value that has nothing to do with F34, F35 & F24.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor