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
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.
Todays Key Takeaways:
- You can do colour calculations in formulas
- You can colour cells using a very simple macro (this is something I’ve used A LOT for heatmapping)
- I spell Colour the Australian (English) way, and wordpress doesn’t like it. So for you, I’ll say color, just this once.