Quantifying Color Choices

Category: Formatting / VBA Functions | [Item URL]

A companion file is available: Click here to download

I got lots of Excel workbooks via email. A significant number of them have some downright ugly color choices. Beauty is in the eye of the beholder, but there's no excuse for making color choices that result in illegible text.

The World Wide Web Consortium (W3C) has created some formulas that can help you determine if your foreground and background colors are legible: Ensure that foreground and background color combinations provide sufficient contrast when viewed by someone having color deficits or when viewed on a black and white screen.

The W3C presents two formulas, each of which returns a value:

• Color Brightness Difference: returns a value between 0 and 255
• Color Difference: Returns a value between 0 and 765

I converted their formulas into VBA functions, and formulas that use these functions are shown in Columns B and C:

To be an acceptable color combination, the Color Difference score should be 500 or greater, and the Brightness Difference score should be 125 or greater. I used conditional formatting to highlight values that exceed these minimums.

Column D has a simple formula that determines if both score meet the minimum requirement.

These formulas seem to work quite well. The color combination deemed Acceptable are all very legible. Bottom line: You can't go wrong with black text on a white background. Reserve the fancy colors for column headers, or for special areas of a worksheet that you want to be noticed.