I have a formula that generates two percent values:
=ROUND(N73/$A$3%,2)&"%"&" / " &ROUND(O73/$A$3%,2)&"%"
The output looks like the following:
-3.79% / -1.24%
The challenge is that either one or both of the two values can be negative. I want a negative value to appear in red.
I have tried simple formatting and conditional formatting, but I just can’t seem to get negative values in red.
I assume that you have to surround both parts (individually) of my formula in some conditional format to get it to work. Any ideas? Thank you,
Condition 1:
Cell value contains "-"
Apply style : Bad
Your suggestion looked promising. In the conditional format window, when I applied your solution, the formula turned red in colour, but the output still remains in black even though both outputs were negative. I played around with other conditions, begins with, less than, etc., but the output remains black, not red.
Create your own cell style with a red font color and specify the name of the created style instead of bad in the conditional formatting dialog.
If you have any difficulties, upload the file example.
By what I know, you can’t set font color to a part of a formula result.
I created a ‘red font’ style on my main sheet, but it had no impact. I then created a new sheet
RedFont.ods (18.7 KB)
and copied the relevant sections. The ‘red font style’ seemed to work, but as you see from my uploaded sheet, it now turns all output red…you can see that I am using the conditional format with the ‘red font’ style.
This is the maximum possible. Conditional formatting is applied to the value of the cell, not to part of the value.
If you use macros, then you can “colorize” part of the text value of the cell, but this does not work when there is a formula in the cell.
(By the way, the latter is true for Excel as well).
See also @LeroyG 's answer.
Vladimir, thanks for the feedback. I guess going forward the easiest solution is just to split the formulas in two and place each in its own column.
1 Like