Forum Discussion

wqhlmethis's avatar
wqhlmethis
Copper Contributor
Jul 18, 2022
Solved

Change color in one column based on value in other column

Hi, I'm new to to SharePoint List so I apologizing in advanced for my l lack of knowledge. 

 

We currently have a list with conditional formatting on the Due Date.

If date is in four, three or two days background color should be yellow

If date is in one day or it is today background color should be red

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if((@currentField <= @now + 86400000), 'red', if((@currentField >= @now + 86400000 && @currentField <= @now + 345600000),'orange',''))"
  }
}

 

 

 

 

Question:

Can the conditional formatting in the due date section be overridden if yes is selected in the completed row? 

 

Goal:

If the user selects YES, in the completed section, the conditional formatting is not displayed any color. 

 

Thanks!

 

 

Lists
microsoft lists
  • ganeshsanap's avatar
    ganeshsanap
    Jul 19, 2022

    wqhlmethis What is the data type of Completed column?

     

    If data type of completed column is Yes/No, use this JSON for due date: 

     

    {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
        "background-color": "=if([$COMPLETED], '', if((@currentField <= @now + 86400000), 'red', if((@currentField >= @now + 86400000 && @currentField <= @now + 345600000),'orange','')))"
    }
}

     

    If data type of completed column is single line of text or choice, use this JSON for due date: 

     

    {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
        "background-color": "=if([$COMPLETED] == 'Yes', '', if((@currentField <= @now + 86400000), 'red', if((@currentField >= @now + 86400000 && @currentField <= @now + 345600000),'orange','')))"
    }
}

     

     Note: Make sure you are using correct internal name of completed column in JSON. Follow this article to get internal name of your SharePoint column: How to find the Internal name of columns in SharePoint Online? 

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Jul 19, 2022

    wqhlmethis What is the data type of Completed column?

     

    If data type of completed column is Yes/No, use this JSON for due date: 

     

    {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
        "background-color": "=if([$COMPLETED], '', if((@currentField <= @now + 86400000), 'red', if((@currentField >= @now + 86400000 && @currentField <= @now + 345600000),'orange','')))"
    }
}

     

    If data type of completed column is single line of text or choice, use this JSON for due date: 

     

    {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
        "background-color": "=if([$COMPLETED] == 'Yes', '', if((@currentField <= @now + 86400000), 'red', if((@currentField >= @now + 86400000 && @currentField <= @now + 345600000),'orange','')))"
    }
}

     

     Note: Make sure you are using correct internal name of completed column in JSON. Follow this article to get internal name of your SharePoint column: How to find the Internal name of columns in SharePoint Online? 

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources