Lightening colors

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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>