Set an automatic color background depending on the HEX value in the cell?

Question

I have figured it out it could be manually done while adding rules to the Conditional Format Rules of a cell: however, I want the all 16,000,000 HEX value colors to appear automatically when I write a HEX value on a cell, so "manually" adding these 16,000,000 HEX values sounds a bit too much! Isn't there a way so all 16,000,000 colors shades automatically find their way in and apply a background color to the cell depending on the HEX value in the cell?

In other word I am trying to get a blue background when I type 0000ff in the cell, then I d like the background to change to the corresponding color when the HEX value of the cell is changed => BGC changes to red when ff0000 is entered, then green when 00ff00, white when ffffff ...etc for the 16,000,000 and up colors possible.

Answer 1

A Worksheet_Change event macro that converts the hexadecimal to an RGB should do this handily. The worksheet's HEX2DEC function should be able to handle the conversion.

Right click the worksheet's name tab and choose View Code. When the VBE opens, paste the following into the code sheet titled something like Book1 - Sheet1 (Code).

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Dim rng As Range, clr As String
    For Each rng In Target
        If Len(rng.Value2) = 6 Then
            clr = rng.Value2
            rng.Interior.Color = _
              RGB(Application.Hex2Dec(Left(clr, 2)), _
                  Application.Hex2Dec(Mid(clr, 3, 2)), _
                  Application.Hex2Dec(Right(clr, 2)))
        End If
    Next rng

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Tap Alt+Q to return to your worksheet. Type any 6 character hexadecimal code into a cell to provide a background color.