• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Using Visio in Education for GIS
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Visualize Complex Processes with Microsoft Visio
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Formatting SharePoint Columns with RGB Color

Published on May 20, 2019 by David Parker

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:

"#"&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:

Related

Filed Under: Accessibility, Color, SharePoint, Visio Tagged With: ShapeSheet

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Comments

  1. Linda Thomas says

    June 1, 2020 at 7:24 pm

    getting syntax error with hex column:
    “#”&MID(“0123456789abcdef”,INT(Red/16)+1,1)&MID(“0123456789abcdef”,(MOD(Red,16)+1),1

    What is wrong and how can this be fixed?

    Reply
    • David Parker says

      June 6, 2020 at 7:37 pm

      It is missing the last “)” …

      Reply
      • Linda says

        June 9, 2020 at 12:04 pm

        thanks, that worked. Now the FontColor one is not working.

        Reply
        • Linda says

          June 9, 2020 at 12:13 pm

          i also added the “)” to the end of the line…still not working.

          Reply

Trackbacks

  1. Testing Visio shape text for WCAG 2.0 pass | bVisual - for people interested in Microsoft Visio says:
    September 4, 2019 at 10:19 pm

    […] Formatting SharePoint Columns with RGB Color […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript LayerManager Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Setup and Deployment Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Validation VBA Video Visio Visio 2007 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • Amazon
  • E-mail
  • Facebook
  • LinkedIn
  • Twitter
  • YouTube

Search this website

Recent posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Copyright © 2025 · Executive Pro on Genesis Framework · WordPress · Log in