Colours and Tints in Excel

It’s been a while since I’ve posted on excel, so long in fact, that my site has been removed, I don’t pay for my domain, and all evidence on the internet is gone..

But, I’m now writing for myself, so that interesting things that I’ve done, are in someway documented, and may be useful for others.

So, today, Colours and Tints. I was asked recently to put together some reports for an organisation that had a defined colour pallette, and to make things more beautiful and consistent, I wanted to use that pallette, tints and all.

So firstly, I needed to get the RGB colours for that pallette. This was easy, as they had the base colours, HOWEVER, they also specified that “tints in 10% increments  can also be used”

Tints. What is a Tint? Well, research that first: https://en.wikipedia.org/wiki/Tints_and_shades

Ok, it’s a whitening? so, how to do that from an RGB value?

Well, my first step was to write a quick macro, that when run on a selected cell, takes the RGB from the three cells next to it, and makes the selected cell that colour

Sub colourCell()
Dim cell As Variant
For Each cell In Selection
With cell.Interior
        .Pattern = xlSolid
        .Color = RGB(cell.Offset(0, -3), cell.Offset(0, -2), cell.Offset(0, -1))
        
End With
Next
End Sub

Colours1

This way, once I’ve layed out the colours with RGB, I can simply select the column after the blue figures, and run the macro, and I can see the colour there.

Next step, working out how tint is calculated. It turns out, this is quite simple. For each of the colour elements, you add a percentage of white.

To make this a formula:

Work out the difference between the current colour & “full colour” (on a 0-255 scale), multiply this by the %tint, and add this value to the colour. Do this for each of the Red, Blue, and Green amounts. The new values are your tinted colour

So, in summary for each of R, G, B: Colour + %tint × (255 − Colour).

Here’s an example I did to do the full tint range for one colour.

Colours2

Todays Key Takeaways:

  1. You can do colour calculations in formulas
  2. You can colour cells using a very simple macro (this is something I’ve used A LOT for heatmapping)
  3. I spell Colour the Australian (English) way, and wordpress doesn’t like it. So for you, I’ll say color, just this once.

 

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: