Change Font color (Conditional formatting)

Question

Hello,I have a percentage variance that appears as a formula derived from 2 other fields.I would love to see a number change color when it's higher than 100% or less than 100% in a given situation, and in green when it's the opposite.Is this something that I place in the formula beneath it? I've been searching where I change and edit aspects of the field, but all I see is display it in Bold.Thank you very much for your support!------------------------------Anthony Wong------------------------------

jeremyanson · Answer

The usual approach - I think! - would be to create a formula rich text field that wraps some formatting around the percentage value. This is a very basic example that changes the background colour to either red or green - in practice you'd want to adjust the styling to suit your needs:

If([Development Status Number]>1,"" & [Development Status Number] & "","" & [Development Status Number] & "")

In cases where you want to add formatting to a field that is entered on a form you can then add both fields to the form and adjust so that the formatted field is shown and the input field is hidden when in display mode and the formatted field hidden when in add or edit mode.

In your case, the field you are adding formatting to is already a formula field, so you would probably just replace the existing field with the new field.

------------------------------
Jeremy Anson
------------------------------

anthonywong1 · Answer

Hello!&nbsp;
Thank you for your quick response, I believe I'm misinterpreting something, or I'm unable to do this due to the previous formula I have?

I attached 2 screenshots, and posted the formula I input.

1 screenshot has what's input and the 2nd with the error message.

The error message mentions additional characters beyond the end of the formula, however I've confirmed there are no blank spaces.

Thank you for your help.

If([Design Variance]>1,""If([Design Variance] & "","" & [Design Variance] & "")

------------------------------Anthony Wong------------------------------

jeremyanson · Answer

Hi,Yes, Quick Base is objecting because the first part of your formula is a complete formula and it doesn't know how to interpret the second part.&nbsp;My suggestion involved creating a second formula field, so you would have:Design Variance([Act. Design Cost] + [Structural Calc Fee])/[Est. Design Cost]... and a second formula field that would allow you to display the result with formatting. e.g.&nbsp;Design Variance for DisplayIf([Design Variance]&gt;1,"&lt;span style='background-color:red;'&gt;" &amp; [Design Variance] &amp; "&lt;/span&gt;","&lt;span style='background-color:green;'&gt;" &amp; [Design Variance] &amp; "&lt;/span&gt;")This means you could still use the Design Variance field in other calculations if needed, while using the second field to display the field with formatting. There's a good chance you'll want to adjust the formula to improve the appearance (if you have some knowledge of HTML you can get very creative) and you'll probably want to multiply the value by 100 to display it as a percentage - perhaps something like this:If([Design Variance]&gt;1,"&lt;span style='background-color:red; color:white; padding: 5px'&gt;" &amp; Round([Design Variance]* 100) &amp; "%&lt;/span&gt;","&lt;span style='background-color:green;color:white; padding: 5px;'&gt;" &amp; Round([Design Variance] * 100) &amp; "%&lt;/span&gt;")------------------------------Jeremy Anson------------------------------

anthonywong1 · Answer

Thank you for the clarification with the 2nd field. I interpreted it as a 2nd formula in the field at the time.I attempted to use the formula you gave me in a few different ways based on your original message and received 2 different errors. Here's the copied formula:
If([Design Variance]&gt;1,"&lt;span style='background-color:red;'&gt;" &amp; [Design Variance] &amp; "&lt;/span&gt;","&lt;span style='background-color:green;'&gt;" &amp; [Design Variance] &amp; "&lt;/span&gt;")I tried it with a Formula-Numeric and received the following error:

I was curious if the term used "span" is intended as a derivative for the max/min for the color I'd like to display, but then I thought it's already referenced in the &gt;1 scenario just before it. If I'm understanding this, span is referring to the color applied to the field.&nbsp;Just to be sure after re-reading your initial segment, I attempted it as a Formula-text field and although it did save the text field simply displayed as shown below.
I was going to attempt the last formula you provided as well, however when I entered it into the system, I received a similar outcome to the previous two.Please let me know if I'm misinterpreting some of the information I need to edit.Thank You again, this is extremely helpful.
&nbsp;------------------------------Anthony Wong------------------------------

miketamoush · Answer

The type of field you select should always be what you anticipate the output being. In this case, your output is text. But not only is it texted, but it will have formatting, so your output is Rich Text. So the field should be a formula Rich Text field.------------------------------Mike Tamoush------------------------------

Forum Discussion

Change Font color (Conditional formatting)