Forum Discussion

AnthonyWong1's avatar
AnthonyWong1
Qrew Member
3 years ago

Change Font color (Conditional formatting)

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
------------------------------
Formulas and functions
Tables and fields

6 Replies

Sort By
  • JeremyAnson's avatar
    JeremyAnson
    Qrew Cadet
    3 years ago
    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,"<span style='background-color:red;>" & [Development Status Number] & "</span>","<span style='background-color:green;>" & [Development Status Number] & "</span>")

    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's avatar
      AnthonyWong1
      Qrew Member
      3 years ago

      Hello! 

      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,"<span style='background-color:red;>"If([Design Variance] & "</span>","<span style='background-color:green;>" & [Design Variance] & "</span>")









      ------------------------------
      Anthony Wong
      ------------------------------
      • JeremyAnson's avatar
        JeremyAnson
        Qrew Cadet
        3 years ago
        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. 

        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. 

        Design Variance for Display

        If([Design Variance]>1,"<span style='background-color:red;'>" & [Design Variance] & "</span>","<span style='background-color:green;'>" & [Design Variance] & "</span>")

        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]>1,"<span style='background-color:red; color:white; padding: 5px'>" & Round([Design Variance]* 100) & "%</span>","<span style='background-color:green;color:white; padding: 5px;'>" & Round([Design Variance] * 100) & "%</span>")


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