Formatting SharePoint Columns with RGB Color

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.

SharePointColumnColourFormat.png

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:
IF((
0.2126*(IF((Red/255)<=0.0393,(Red/255)/12.92,((Red/255)+0.055)/1.055)^2.4)+
0.715*(IF((Green/255)<=0.0393,(Green/255)/12.92,((Green/255)+0.055)/1.055)^2.4)+
0.0722*(IF((Blue/255)<=0.0393,(Blue/255)/12.92,((Blue/255)+0.055)/1.055)^2.4))>=0.175,
"black","white")
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 tinited versions of the SharePoint ones above:
VisioColorsTinted
Advertisements

One Response to “Formatting SharePoint Columns with RGB Color”


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

JackBinnall

O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

davecra.wordpress.com/

Azure Solutions for Office 365, and more...

Rob Fahrni

I AM FAHRNI

johnvisiomvp

Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people

%d bloggers like this: