VBA Tip: Convert a hexadecimal color to a Color value

This function is used to convert colors in hexadecimal format (for example: #26cfa7) into colors that can be used in VBA.

Note that this function accepts colors in hexadecimal format with or without # at the beginning, it is compatible with all versions of Excel and it returns -1 in case of error.


To be able to use colors in hexadecimal format in VBA, first copy this function into a module:

Function hexa_color(ByVal hexa) 'Returns -1 in case of error

    'Convert a hexadecimal color to a Color value - Excel-Pratique.com
    'www.excel-pratique.com/en/vba_tricks/hexadecimal-color-function.php

    If Len(hexa) = 7 Then hexa = Mid(hexa, 2, 6) 'If color with #
   
    If Len(hexa) = 6 Then

        num_array = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "a", "b", "c", "d", "e", "f")
       
        char1 = LCase(Mid(hexa, 1, 1))
        char2 = LCase(Mid(hexa, 2, 1))
        char3 = LCase(Mid(hexa, 3, 1))
        char4 = LCase(Mid(hexa, 4, 1))
        char5 = LCase(Mid(hexa, 5, 1))
        char6 = LCase(Mid(hexa, 6, 1))
       
        For i = 0 To 15
            If (char1 = num_array(i)) Then position1 = i
            If (char2 = num_array(i)) Then position2 = i
            If (char3 = num_array(i)) Then position3 = i
            If (char4 = num_array(i)) Then position4 = i
            If (char5 = num_array(i)) Then position5 = i
            If (char6 = num_array(i)) Then position6 = i
        Next
       
        If IsEmpty(position1) Or IsEmpty(position2) Or IsEmpty(position3) Or IsEmpty(position4) Or IsEmpty(position5) Or IsEmpty(position6) Then
            hexa_color = -1
        Else
            hexa_color = RGB(position1 * 16 + position2, position3 * 16 + position4, position5 * 16 + position6)
        End If
       
    Else
        hexa_color = -1
    End If
   
End Function

Then simply use the hexa_color function to set (or retrieve) the value of Color.

Example to color the A1 cell with the #1b9426 color:

Sub example()
    Range("A1").Interior.Color = hexa_color("#1b9426")
End Sub