On an ongoing project, I had to create lighter shades of a color. Since we were working with Excel 2007, I decided to use Range.Interior.TintAndShade to achieve the desired result. TintAndShade is a number between -1 and 1 that lightens or darkens a color. Initial tests showed it seemed to work fine. In the image below, the first row contains 5 ‘base’ colors. Each subsequent row contains a slightly lighter shade of that color until eventually we get white.
The code to get the shades for 1 column is below. Select any number of cells in a column with the ‘base’ color as the first cell and run doTintAndShades. The above example used a selection of 8 cells in each column.
Option Explicit Sub doTintAndShades() Dim I As Integer, Rng As Range Set Rng = Selection For I = 2 To Rng.Cells.Count With Rng.Cells(I).Interior .Color = Rng.Cells(1).Interior.Color .TintAndShade = (I - 1) / (Rng.Cells.Count - 1) End With Next I End Sub
Done, one would think. Well, not quite. With Yellow as the base color I got:
After convincing myself that it wasn’t me nor my understanding of TintAndShade, I decided to separately lighten each of the primary colors Red, Green, and Blue.
Sub lightenRGB() Dim I As Integer, Rng As Range Dim R As Byte, G As Byte, B As Byte Set Rng = Selection With Rng.Cells(1).Interior R = .Color Mod 256 G = .Color \ 256 Mod 256 B = .Color \ (CLng(256) * 256) End With For I = 2 To Rng.Cells.Count With Rng.Cells(I).Interior .Color = RGB(R + (255 - R) * (I - 1) / (Rng.Cells.Count - 1), _ G + (255 - G) * (I - 1) / (Rng.Cells.Count - 1), _ B + (255 - B) * (I - 1) / (Rng.Cells.Count - 1)) End With Next I End Sub
with the result:
I then decided to check Excel 2010. The TintAndShade code works just fine. So, apparently, Microsoft fixed whatever ailed the Excel/Office 2007 TintAndShade property.