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

[Laurie88]

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 added a mock up of how it would look ideally:

Basically we need to have a quick view of the status of the date through formatting. I'd like to hide the condition column as there will be quite a few of them (mulitiple date columns). I can then manually choose if a date is provisional or confirmed and other users will be able to see the status of the date only from the date cell.

Any help would be really appreciated!

[#2]

@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.

[#3 · Accepted solution]

@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

[#4]

@RobElliott That works perfectly. Thank you so much!

[#5]

Thank you so much for the help! @Paul Hunt - Cimares