J-Walk & Associates, Inc.

Color Contrast Functions Posted

How many times have you seen a worksheet that looks something like this?

I get quite a few Excel workbooks from others, and poor choice of colors seems to be a fairly common problem.

Today I posted a tip that describes two VBA functions that can quantify the legibility of foreground and background color choices: Quantifying Color Choices.

It's based on formulas from the W3C, to determine accessibility of Web pages. But it seems to work just as well for Excel worksheet.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By AlexJ. Comment posted 25 August, 2008 12:45pm

   Can we take Bold format into account? It can certainly change the readability.
2. By John Walkenbach. Comment posted 25 August, 2008 12:57pm

   Yes, I noticed that bold text makes a big difference in readability.
   
   Keep in mind that these "scores" are just guidelines, and aren't laws or anything.
3. By Jon Peltier. Comment posted 25 August, 2008 7:39pm

   These are guidelines for items which are subsequently copied in grayscale, and for items as viewed by individuals with color vision deficiencies.
   
   With my approximately normal color vision, I found the combination in row 7 (rated "Acceptable") harder to read for example than the combination in row 11 ("No Good").
4. By BrianR. Comment posted 26 October, 2008 11:30am

   Out of laziness, I wrote the macro below to change the font colour of the selected cell(s) to either black or white, depending on which was the most visible. Of course, this doen't give the maximum contrast, but at least the result is reasonably legible.
   
   

   Sub Visible_Font_Colour()
' For each cell in a selection, calculate its luminance and change its font colour (using Black and White only) so that it's visible.
' Brian Redmond

Dim xCell As Range
Dim xInterior As Long
Dim xLuminance As Long
Dim xresponse As Long

For Each xCell In Selection
    
    xInterior = xCell.Interior.Color
    xLuminance = Int(xInterior / 16 ^ 4) * 0.0721 _
                    + Int((xInterior - Int(xInterior / 16 ^ 4) * 16 ^ 4) / 16 ^ 2) * 0.7154 _
                    + (xInterior Mod 16 ^ 2) * 0.2125
    xCell.Font.ColorIndex = IIf(xLuminance / 255 < 0.64, 2, 1)

Next

End Sub 

   
   
   Regards,
   Brian.