Introduction

A useful trick with SharePoint lists is to use colour coding so you can quickly see the status of the item.

For example you may have a calendar list to which you’ve added a Status column using a choice for Complete, Started, Pending.

————

The example below shows how to create a new column which will show colours for each status – Complete=Green, Started=Orange and Pending=Red

Create a new column StatusColour in the SharePoint list using Calculated column type and enter formula as appropriate using data type Number

To change background colour:

="<div style='text-align:left; background-color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

To change font colour:

="<div style='text-align:left; color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

To change font and background colours:

="<div style='text-align:left; background-color:"&IF(Status="Completed","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"; color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

Note: When using a column with spaces in the name you need to wrap in square brackets e.g. Current Status should be defined in IF as [Current Status] and at end of function as &[Current Status]&

You can use HTML colour codes as well as names.

e.g. set background and font colours based on Status column:

="<div style='text-align:left; background-color:"&IF(Status="Complete","#008000",IF(Status="Started","#800080",IF(Status="Pending","#FF0000")))&"; color:"&IF(Status="Complete","white",IF(Status="Started","white",IF(Status="Pending","white")))&"'>"&Status&"</div>"