Sharepoint list - highlight a number in column if less than a number in another column

Question

Hi everyone,&nbsp;I'm pretty new to Sharepoint but I've been trying to push myself to use it more. I'm currently creating a list which includes two columns, an On Hand column and a Reorder At column. I would like to format the cells in a way that a cell in the On Hand column would become highlighted if the number within it became equal to or less than the cell in the adjacent column (Reorder At).&nbsp;&nbsp;So for example, the 2 below should be highlighted since it is less than the 4 to the right.&nbsp;I've been trying to find a way to create a rule in the column formatting to do this but so far am unable to. Any help would be greatly appreciated! Thank you

robelliott · Answer

hgg48&nbsp;you can do this with JSON column formatting. Format the OnHand column, select Advanced Mode and add the following:&nbsp;&nbsp;{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$OnHand] &lt;= [$ReorderAt], 'red', 'green')",
    "font-weight": "=if([$OnHand] &lt;= [$ReorderAt], 'bold', 'normal')"
  }
}&nbsp;&nbsp;&nbsp;The result is:&nbsp;RobLos GallardosIntranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

hgg48 · Answer

RobElliottThank you very much for the quick reply, very much so appreciate it. I tried adding what you wrote into the advanced mode editor but the numbers in the OnHand column show up as all green for me. Can you please tell me what I did wrong? Thanks!

robelliott · Answer

hgg48&nbsp;in your list settings check what the internal name is. In my columns I didn't use spaces in the column name - I never do - whereas you might have spaces in your column name. So check that and use the internal column name in your JSON column formatting.&nbsp;&nbsp;RobLos GallardosIntranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

hgg48 · Answer

It looks like I did create the columns with internal names containing spaces. I tried to put spaces in the JSON column formatting to reflect the internal name of On Hand and Reorder At but the columns just turned all blank. Is there a special way I need to handle spaces? Thank you for your continued help.

robelliott · Answer

hgg48&nbsp;you need to use _x0020_ in place of each space (and this is also shown in the Field= in List Settings). So your JSON code would look like this:{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$Current_x0020_Stock] &lt;= [$Order_x0020_More], 'red', 'green')",
    "font-weight": "=if([$Current_x0020_Stock] &lt;= [$Order_x0020_More], 'bold', 'normal')"
  }
}&nbsp;Or you could use the&nbsp;@currentField as follows which gives the same result:{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if(@currentField &lt;= [$Order_x0020_More], 'red', 'green')",
    "font-weight": "=if(@currentField &lt;= [$Order_x0020_More], 'bold', 'normal')"
  }
}&nbsp;&nbsp;&nbsp;RobLos GallardosIntranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)&nbsp;

Forum Discussion

Sharepoint list - highlight a number in column if less than a number in another column

5 Replies

Resources