How to color code the SharePoint list column value based on a condition?

Question

I have a requirement to color code a SharePoint list column value based on a condition. Please see the below screenshot.

Basically as shown in the above image, I want to compare the value of columns 4th Qtr Goal vs 4th Qtr Actual, if actual value is greater than goal than in Qtr staus column for the specific list item, I want to insert text called 'Goal met' in green color text otherwise I want to insert Goal did not meet with red color.

Also this should be dynamic so whenever the value in those columns change it should compare the values again and print the text in the QTR status column accordingly. This logic must be applied to all the list items present in the list, so probably on page load the code would compare 4th qtr goal vs 4th qtr goal actual and do the needful as I mentioned above.

I would want to achieve this using JSOM, can someone please help me with the code, many thanks in advance.

Answer 1

To do this is a 2 step process:

Start by creating a calculated column that displays the text value you want eg:

=IF ([4th Qtr Goal] > [4th Qtr Actual], "Goal did not meet", "Goal Met")

This cell should automatically re-calculate every time the item is updated.

Then you can use JavaScript on your view page to change the css of the cells to change the color

<script>
function CellColour()
{
var cell = $('td'); 

cell.each(function() {                    //loop through all td elements 

var cell_value = $(this).html();          //get the value

if (cell_value == "Goal did not meet")    //find cells with Goal did not meet in them
    $(this).css({'color' : '#c94444'});   // changes color to red.
    if (cell_value == "Goal met")         //find cells with Goal met in them
    $(this).css({'color' : '#6dc944'});   // changes color to green.

});

}
_spBodyOnLoadFunctionNames.push("CellColour");

</script>

Answer 2

You can try Client Side Rendering along with a calculated column to achieve your goal. First create a calculated column say isGoalMet with formula as

=IF ([4th Qtr Goal] > [4th Qtr Actual], "Goal did not meet", "Goal Met")

Then based on this calculated column value change the color using CSR Ref CSR

How to use CSR

        (function () {

        var statusFieldCtx = {};
        statusFieldCtx.Templates = {};
        statusFieldCtx.Templates.Fields = {
            "isGoalMet": {
                "View": StatusFieldViewTemplate
            }
        };

        SPClientTemplates.TemplateManager.RegisterTemplateOverrides(statusFieldCtx);
    })();

    function StatusFieldViewTemplate(ctx) {



        if (ctx.CurrentItem.isGoalMet =='Goal Met')
        {
            return "<div style="color:red">"+ctx.CurrentItem.isGoalMet +"/div>";
        }
        else
        {
            return "<div style="color:green">"+ctx.CurrentItem.isGoalMet +"/div>";
        }
}