Site icon bVisual

Formatting SharePoint Columns with RGB Color

David Parker

I wrote about ensuring high contrast between text and the background of Visio shapes in my last article. The RGB values actually came from an imported SharePoint list that has a lookup to a list that contains the RGB values of named colours. Having solved how to display the correct text colour for high contrast in Visio, I wanted to the same in a SharePoint column, but first I had to learn how to use the RGB values to format the background colour.


My good friend Chris Roth, aka VisGuy, has already produced Visio ShapeSheet formulas to convert RGB values to Hex values. so I adaptively re-used his idea to create a formula that I could use in a SharePoint calculated column, called Hex.
First, I compressed his multiple User-defined Cells rows into a single formula:

="#"&
INDEX(INT(RED(FillForegnd)/16),"0;1;2;3;4;5;6;7;8;9;a;b;c;d;e;f")&
INDEX(INT(MODULUS(RED(FillForegnd),16)),"0;1;2;3;4;5;6;7;8;9;a;b;c;d;e;f")&
INDEX(INT(GREEN(FillForegnd)/16),"0;1;2;3;4;5;6;7;8;9;a;b;c;d;e;f")&
INDEX(INT(MODULUS(GREEN(FillForegnd),16)),"0;1;2;3;4;5;6;7;8;9;a;b;c;d;e;f")&
INDEX(INT(BLUE(FillForegnd)/16),"0;1;2;3;4;5;6;7;8;9;a;b;c;d;e;f")&
INDEX(INT(MODULUS(BLUE(FillForegnd),16)),"0;1;2;3;4;5;6;7;8;9;a;b;c;d;e;f")

Then I converted that formula into one that could be used in the hex SharePoint column:

"#"&MID("0123456789abcdef",INT(Red/16)+1,1)&MID("0123456789abcdef",(MOD(Red,16)+1),1)&MID("0123456789abcdef",INT(Green/16)+1,1)&MID("0123456789abcdef",(MOD(Green,16)+1),1)&MID("0123456789abcdef",INT(Blue/16)+1,1)&MID("0123456789abcdef",(MOD(Blue,16)+1),1)

I also took my ShapeSheet formula from the last article to create a version that can be used in a SharePoint calculated column called FontColor:

"#"&MID("0123456789abcdef",INT(Red/16)+1,1)&MID("0123456789abcdef",(MOD(Red,16)+1),1)

I found an answer to a Stack Overflow question about creating tint from RGB values useful in creating further calculated columns for 20%, 40%, 60% and 80% tinted variations of each colour. All I had to do is change the 0.2 to 0.4, 0.6 and 0.8 respectively in the following formula:

="#"&
MID("0123456789abcdef",INT((Red+(255-Red)*0.2)/16)+1,1)&
MID("0123456789abcdef",(MOD((Red+(255-Red)*0.2),16)+1),1)&
MID("0123456789abcdef",INT((Green+(255-Green)*0.2)/16)+1,1)&
MID("0123456789abcdef",(MOD((Green+(255-Green)*0.2),16)+1),1)&
MID("0123456789abcdef",INT((Blue+(255-Blue)*0.2)/16)+1,1)&
MID("0123456789abcdef",(MOD((Blue+(255-Blue)*0.2),16)+1),1)

So now I had calculated values for all of my columns, and all I need to do now was apply JSON column formatting to each column. For example, the Title column has the following formula:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "font-weight": "bold",
    "color": "[$FontColor]",
    "background-color": "[$Hex]"
  }
}

The Tint20, Tint40, Tint60 and Tint80 columns all have a similar formula applied:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "font-weight": "bold",
    "color": "[$FontColor]",
    "background-color": "@currentField"
  }
}

By the way, I notice that the MSOTINT(…) function returns slightly more colourful appearance than the JSON tint formulas above.  I don’t really know why…. these are the Visio tinted versions of the SharePoint ones above:

Exit mobile version