Forum Discussion

Laurie88's avatar
Laurie88
Copper Contributor
Jan 26, 2021
Solved

SharePoint List - Highlight a cell based on status of a different cell

Hi there,   I would like to format some date cells on a SharePoint List so they are highlighted a different colour if the corresponding cell on the adjacent column has 'Confirmed' entered. I've add...
SharePoint Online
  • RobElliott's avatar
    RobElliott
    Jan 26, 2021

    Laurie88 very straightforward with JSON column formattingClick on the NJ Launch dropdown > column settings >format this column, then click on the advanced mode link and add the following:

     

    {
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"padding-left": "14px",
"background-color": "=if([$Condition] == 'Confirmed','#00c36f', (if([$Condition] == 'Provisional','#ff7e00', '#d0d0d0')))",
"color": "white"
}
}

     

     

    and the result looks like this and you can then hide the Condition column:

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User

Paul Hunt - Cimares's avatar
Paul Hunt - Cimares
MVP
Jan 26, 2021

Laurie88 You can't do that using the Format Column through the UI, but you can do it with a little bit of JSON based conditional formatting. The following should do what you need and just get's pasted into the Format area of the Column settings.

 

The only downside is that the field you trigger on must be in the view.

 

Just change the colours to suite by replacing Blue/Red with a hashcode like #ec3c35

 

{
"$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType":"div",
"txtContent":"@currentField",
"style":{
"background-color": "=if([$Condition] == 'Confirmed','blue','red')"
}
}

 

You'll need to do this for each Date column and vary the trigger column name ($Condition) to match. Note if you have spaces in your column names, it'll look like $Condition_X0020_Column 

 

More details on this approach here: Use column formatting to customize SharePoint | Microsoft Docs

 

Paul.

Resources