locked
SharePoint 2013 calculated column formula to change text color based on another column RRS feed

  • Question

  • So I have 2 columns. Column 2 is a choice column (drop down), Column 1 is a single line of text column.

    I would the color of column 1 to change based on what is selected in column 2 dropdown soooo..

    Column 1     Column 2

    Apples          Ripe

    Cherries       Not Ripe

    Grapes         Ripe

    I wrote this in a calculated column called CreateColor

    =IF(Column 2="Ripe", 'Column 1=<div style=color:"Green;">', 'Column 1=<div style=color:"Red;">')

    Of course it didn't work. Can someone help? I saw a lot of examples that allow you to change the dropdown but not changing another column based on dropdown.


    • Edited by TinkW Tuesday, June 30, 2015 3:10 PM
    • Moved by Hemendra Agrawal Wednesday, July 1, 2015 4:36 AM SP 2013
    Tuesday, June 30, 2015 3:08 PM

Answers

  • Thank you for your reply but this doesn't help me. I don't want to change the color of a drop down. I want to change the color of one text column (Column 1) based on the drop down selection (Column 2).

    I created a 3rd Column, a multi text field, and in designer I used the string builder and it worked.

    I wrote:

    If Column 2 equals Ripe

      set Column 3 to <div style="color:green;">Column 1</div>

    Else

      set Column 3 to <div style="color:red;">Column 1</div>
    • Marked as answer by Victoria Xia Monday, August 10, 2015 9:14 AM
    Tuesday, June 30, 2015 5:43 PM

All replies

  • You would need to use SharePoint Designer and add conditional formatting.

    kashif

    Tuesday, June 30, 2015 3:49 PM
  • Hi Tink,

    To have color coding in the sharepoint list along with the calculated column you have to do few additional CSS/JScript hack, so that the html would recognize the logic.

    http://blog.pathtosharepoint.com/2008/12/09/color-coding-more-examples/

    http://thinketg.com/creating-a-stoplight-color-coded-status-in-a-sharepoint-list/

    Above URL would help you to start with for your scenario.

     


    Sivabalan

    Tuesday, June 30, 2015 4:42 PM
  • I can't seem to get that to work for me in 2013, maybe because I'm use to using the designer view in 2010 when I do conditional formatting. But I found a solution, I did use designer and create a workflow. Thank you for replying.
    Tuesday, June 30, 2015 5:43 PM
  • Thank you for your reply but this doesn't help me. I don't want to change the color of a drop down. I want to change the color of one text column (Column 1) based on the drop down selection (Column 2).

    I created a 3rd Column, a multi text field, and in designer I used the string builder and it worked.

    I wrote:

    If Column 2 equals Ripe

      set Column 3 to <div style="color:green;">Column 1</div>

    Else

      set Column 3 to <div style="color:red;">Column 1</div>
    • Marked as answer by Victoria Xia Monday, August 10, 2015 9:14 AM
    Tuesday, June 30, 2015 5:43 PM
  • Your HTML/CSS is wrong:

    Create a Calculated Column set the datatype to Number, use the Formula:

        ="<div style=""color:" & IF([Column 2]="Ripe", "Green" , "Red") & ";font-weight:bold;"">" & [Column 1] & "</div>"

    • Proposed as answer by Victoria Xia Thursday, July 23, 2015 5:03 AM
    Saturday, July 11, 2015 7:49 AM
  • your formula works great.  How would I do it to make more than one color in a column depending on text in another.  For instance; Three drop down choices to select in a column: High, Medium, and Low.  I used the formula to make High red but, I cant figure out how to make it work with Medium to Yellow and Low black in the same column.  Can you help, please?
    Thursday, October 6, 2016 7:06 PM
  • add conditional formatting using SharePoint Designer.  It would look something like this:

    <xsl:if test="normalize-space($thisNode/@My_Column) = 'High'" ddwrt:cf_explicit="1">font-size: larger; color: #FFFFFF; background-color: red;</xsl:if>
    <xsl:if test="normalize-space($thisNode/@My_Column) = 'Medium'" ddwrt:cf_explicit="1">color: Black; background-color: yellow;</xsl:if>
    <xsl:if test="normalize-space($thisNode/@My_Column) = 'Low' ddwrt:cf_explicit="1">font-size: larger; color: #FFFFFF; background-color: green;</xsl:if>

    Add one conditional format and then replace it with multiple IF conditions.


    kashif

    Monday, October 10, 2016 6:30 AM
  • Have a look on below article:

    https://blog.splibrarian.com/2012/06/06/using-calculated-columns-to-add-color-coding-to-your-sharepoint-lists/

    Mahi


    Please Mark as answer if found useful :)

    Monday, October 10, 2016 7:42 AM
  • your formula worked for a while.

    now it is displaying the code (see below) rather than computed (formatted) text.

    <div style="color:Green;font-weight:bold;">Report</div>

    Sunday, June 18, 2017 4:40 AM
  • Try this. My field is titled 'ProjectStatus'. It has three values: Red, Green and Orange. The new calculated field works with the below

    ="<div style=""color:" & IF([ProjectStatus]="Green", "Green", IF([ProjectStatus]="Red", "Red", "Orange")) & ";font-weight:bold;"">" & [ProjectStatus]
     & "</div>"


    KP




    Thursday, September 7, 2017 8:22 PM
  • Microsoft patched SharePoint so you couldn't use HTML/CSS in the calculated field formulas.

    Thursday, August 29, 2019 5:08 AM