RGB to HEX in Excel
The aim of this tutorial is to demonstrate how Microsoft Excel can be used to illustrate colours graphically, by converting codes from the RGB Colour Model into hexadecimal colour codes and then displaying the matching colour beside each.
I recently needed this functionality to allow me to describe colour for written reports, but there are many applications for such a utility.
Step 1: Set up the Excel sheet
Set up a new Excel sheet with columns named as follows:
- Column A = “R”
- Column B = “G”
- Column C = “B”
- Column D = “Hex”
- Column E = “Colour”
When complete, this worksheet will enable you to enter RBG values into the first three columns and then the HEX code will be output in Column D with a colour sample in Column E.
Step 2: Format as table and enter data
On the worksheet, select a range of cells that you want to quickly format as a table. On the Home tab, in the Styles group, click Format as Table.
Enter RBG values into the first three columns and save the workbook in XLSM (Excel Macro-Enabled Workbook) format.
Step 3: Derive HEX values
In the second row of Column 4 (“HEX”), insert the following formula:
HEX values should now be displayed to match each RGB combination entered. The formula uses the
DEC2HEX function to convert RGB values into HEX codes, but needs to be further modified in order to allow for leading zeroes.
Step 4: Insert the VBA scripts
Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled. In the Visual Basic for Applications toolbar, go to Insert > Module
Paste the following code (based on the work of “pnuts“):
Sub ColourMyCells() Dim LastRow As Integer On Error Resume Next Application.DisplayAlerts = False LastRow = ActiveSheet.UsedRange.Rows.Count On Error GoTo 0 Application.DisplayAlerts = True If LastRow = 0 Then Exit Sub Else Dim i As Integer For i = 2 To LastRow Cells(i, 5).Interior.Color = RGB(Cells(i, 1), Cells(i, 2), Cells(i, 3)) Next i End If End Sub
Next, double-click on “Sheet1” under “Microsoft Excel Objects” and paste in the following code:
Private Sub Worksheet_Change(ByVal Target As Range) ColourMyCells End Sub
This second code is for a worksheet change event that tells Visual Basic to execute the
ColourMyCells script whenever any change is made to a cell in Sheet1.
The Excel sheet should now display colours beside the HEX codes.