SharePoint List Conditional Formatting Within a Range of Another Column

Question

I have a list where I'm recording the scores of three reviewers. Each score needs to be within 10 of both of the other two scores. I'd like scores that are outside of that range (too far from one score or another) to turn red. To start with I was just comparing one column ($Reviewer1Score) with one other column ($Reviewer2Score), and then I'll be able to add the second comparison.

So in lay terms, I need the background to turn red if Reviewer1Score is "<Reviewer2Score-10" or ">Reviewer2Score+10". I'm just having no luck turning that into code.

Answer 1

If you are trying to highlight the cell/column based on conditions mentioned in your question, you have to use the SharePoint JSON column formatting.

You can use below JSON for your Reviewer1Score column based on comparison with Reviewer2Score column (assuming both are of type Number):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "padding-left": "10px",
    "font-weight": "bold"
  },
  "attributes": {
    "class": "=if(@currentField < [$Reviewer2Score]-10 || @currentField > [$Reviewer2Score]+10, 'ms-bgColor-red ms-fontColor-white', '')"
  }
}

Where [$Reviewer2Score] is the internal name of your list column. You can find the exact internal name of your SharePoint columns by following this article: How to find the Internal name of columns in SharePoint Online?

Output:

Similarly, you can add more conditions based on other columns in the SharePoint list.

Answer 2

Assuming you meant the column background, here is how to do so.

1. Click on "Format current view" from the top right corner under "All Items"

2. Choose "Format columns" and your respective column where you want to change the background color. In this example it is Reviewer2Score.

3. Enter the following JSON. The key piece for formula will be this excerpt:

=if([$Reviewer1Score] < (@currentField - 20) > [$Reviewer1Score], 'ms-bgColor-red', 'ms-bgColor-white')

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "div",
      "style": {
        "position": "absolute",
        "width": "100%",
        "height": "100%",
        "opacity": "0.5"
      },
      "attributes": {
        "class": "=if([$Reviewer1Score] < (@currentField - 20) > [$Reviewer1Score], 'ms-bgColor-red', 'ms-bgColor-white')"
      }
    },
    {
      "elmType": "div",
      "txtContent": "@currentField"
     
    }
  ]
}