[Solved] Text color in =IF result

Discuss the spreadsheet application
Post Reply
cwr1946
Posts: 10
Joined: Sat Sep 25, 2010 1:07 am

[Solved] Text color in =IF result

Post by cwr1946 »

I wish to have a cell's text color determined as the result of an =if test. I've tried several variations, all with the same result of black text as the result - the text content is correct but the color is always black.

Here's a sample of what I've tried:

G23 is: =if(H21<0;G25;G24)
where:
G24 is GREEN: "More Time Available"
G25 is RED: "Too Much Time Used"
and H21 is a Time value: hh:mm:ss.nnn

How can I get the results in cell G23 to be GREEN or RED text depending on the results of the IF test?
Last edited by cwr1946 on Sat Sep 25, 2010 3:45 am, edited 1 time in total.
Open Office 3.2.1 Build 9502; Windows 7 Professional
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Text color in =IF result

Post by Charlie Young »

First, using Format > Styles and Formatting, create a "Green" style and a "Red" style, which is most simply accomplished using the middle button on the right in the dialog, "New style from selection," pointing in turn to G24 and G25. The styles can be called anything you like (within the naming rules), but "Green" and "Red" will serve nicely here. Then there are a couple of possibilities:

1) Use Format > Conditional Formatting on G21, setting condition 1 to "Formula is" with formula $Sheet1.$H$21>0 and Cell style to "Green," and condition 2 to "Formula is" with formula $Sheet1.$H$21<= 0 and Cell style to "Red." (You can accommodate the = 0 condition as per your preference.)

2) Enter a formula like

Code: Select all

=T(STYLE(IF(H21<0;"Red";"Green"))) & IF(H21<0;G25;G24)
in G21. See help on the STYLE function about this. The T is to prevent a data type mismatch.

I have implemented these things in the attached sheet, where I have illustrated both methods with the STYLE formula is in G20.
Attachments
redgreen.ods
Red or Green text
(7.29 KiB) Downloaded 1229 times
Apache OpenOffice 4.1.1
Windows XP
cwr1946
Posts: 10
Joined: Sat Sep 25, 2010 1:07 am

Re: Text color in =IF result

Post by cwr1946 »

Excellent! Thanks for the quick reply and solution.
Open Office 3.2.1 Build 9502; Windows 7 Professional
Post Reply