Get RGB values from Excel cells

I do custom Crystal reports for customers as a side-job and was presented with an interesting challenge this last week.

The issue was that the customer wanted to color-code fields in their Crystal report based on pre-defined fields. They provided a spreadsheet with the colors, and it was my job to get them somewhere that the report could use.

In the end, I had to use some VBA and some new SQL tables, but it works, and works well.

The first challenge was this - in Crystal reports you really only have a couple of conditional formatting options for color, either their color constants or an RGB scheme:
// This conditional formatting formula must return one of the following Color Constants:
//
// Color (red, green, blue)
// crBlack
// crMaroon
// crGreen
// crOlive
// crNavy
// crPurple
// crTeal
// crSilver
// crRed
// crLime
// crYellow
// crBlue
// crFuchsia
// crAqua
// crWhite
// crNoColor

Since the customer was providing the colors, I knew I had to go with RBG. But how to pull those values out of the provided spreadsheet? Enter VBA.

Thanks to the power of the Google, I took and modified some VBA code to pull the unique R code, G code, and B code (since Crystal expects 3 distinct strings, not a single string in their RBG formula) for each cell:

Function showR(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showR = Application.Evaluate("=Hex2dec(""" & Right(myStr, 2) & """)")

End Function

Function showG(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showG = Application.Evaluate("=Hex2dec(""" & Mid(myStr, 3, 2) & """)")

End Function

Function showB(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showB = Application.Evaluate("=Hex2dec(""" & Left(myStr, 2) & """)")

End Function

That worked like a charm. I then dumped those values into the database, where I was able to use conditional formatting to push those values out of the database into the field.

After a large amount of work, it works like a charm.