We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Use IF statements to change text colour? [closed]

asked 2017-02-19 06:55:50 +0200

appreciatethehelp gravatar image

updated 2021-06-15 16:52:46 +0200

Alex Kemp gravatar image

Hello, I have a budget document that I have made.

In the cell displaying the total income result after expenses, I want the cell to display the numeric text in GREEN if the number is 0 or above, or RED if the number is below zero/ "in the negatives".

I am guessing some kind of IF function would do the job, I just don't know how to make the IF function change the colour of the numeric text.

Let's say the cell containing the total was cell A10, the formula might be something like this…

=IF(A10>=0, "green", "red")

Of course, this formula would only display the word "green" or "red", instead of actually changing the colour of the number displayed…

Also, how do I use the IF statement in conjunction with the calculation needed to produce the total? Let's say that cell A8 is the cell containing total income, cell A9 contains the total expenses, and cell A10 contains the result of income-expenses (A8-A9). How do I separate the IF function needed to change the colour of the numeric text from the subtraction formula used to produce the total? Will an ampersand do, like this?

A8-A9 & =IF(A10>=0, "green", "red")

Thanks for your time and effort!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by appreciatethehelp
close date 2017-06-26 14:38:13.036287

3 Answers

Sort by » oldest newest most voted

answered 2017-03-25 08:35:41 +0200

To change the text color based on if the value is positive, negative or 0, you may use format codes, like this:


For this specific use case, I suppose it's superior to using conditional formatting, that are much more powerful and flexible, but overkill here.

edit flag offensive delete link more


To input Format Codes for a given cell: Format > Cells > Numbers tab, then enter the desired Format Code in the box provided at the bottom of the page.

appreciatethehelp gravatar imageappreciatethehelp ( 2020-10-23 05:49:41 +0200 )edit

answered 2017-02-19 07:07:22 +0200

robleyd gravatar image

Conditional formatting is what you want. Press F1 with a Calc document open and search conditional format

edit flag offensive delete link more


I couldn't get the text to change colour using conditional formatting, but I could get the backgroundbehind the text to do so. If there is a way to do so using conditional formatting, could someone please spell it out for me? I can't seem to find any options to change text colour using conditional formatting. I found another way to make the text go red when the number within the cell is below 0: Format > Cells > Numbers tab > Negative numbers red

appreciatethehelp gravatar imageappreciatethehelp ( 2017-03-24 01:32:31 +0200 )edit

Now I just need to figure out how to make positive numbers go green… any suggestions?

appreciatethehelp gravatar imageappreciatethehelp ( 2017-03-24 01:33:04 +0200 )edit

answered 2017-03-24 23:22:26 +0200

EasyTrieve gravatar image

updated 2017-03-24 23:26:16 +0200

Without using a macro you can only style by value. For this select the cells you want to color, then go here:

Menu> Format Conditional Formatting Manage...


Edit Apply Style New Style...


Font Effects tab Font color: (select a color).

Learning about Macros is harder, much harder but much is possible with macros (e.g. Basic code).

edit flag offensive delete link more


This just isn't working for me. It applies the colour to several cell ranges that are not selected, and it applies the colour to cells that have different values than specified. HELP!

dejayajay gravatar imagedejayajay ( 2020-04-10 04:25:27 +0200 )edit

Question Tools



Asked: 2017-02-19 06:55:50 +0200

Seen: 99,544 times

Last updated: Mar 25 '17