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.

Bibbie

=sparkline(1,{"charttype","bar";"color1",A1}) – Weinstein

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.

        

Hamachi

your code while longer, seems to give the expected result. I failed to explain why mine inverts Red and Blue. Even though I tried Target.Text instead of Target.Value! – Alecto

Thanks, I really appreciate it. – Bibbie

Thanks a lot, it works like a charm on Excel. Would it be possible to reproduce it on Google Excel Sheet with a function added to the conditional format rules? – Bibbie

@Bibbie - While I'm no expert on Google-Sheet event 'triggers', the conditional formatting works pretty much the same as Excel's. In short, the resulting color change cannot be dynamic; it must be set beforehand and remain static. That translates into a necessity to have one CF rule for every color you want to be able to reproduce and the sheer volume of possibilities makes the method impractical. – Hamachi

Thanks again Jeeped. So perhaps a CF rule with a range of HEX values with a close color match assigned to might do the trick and should avoid inputting millions of CF rules... or translating the VBA rule into javascript perhaps? – Bibbie

This works great, however it only works at the first time. When the Hex value in a cell is changed as a result of a formula from another cell, the new Hex value in the very same cell does not change the background color of this cell. Instead, the old background remains in the cell and the new Hex value appears in the cell (so conflict between the HEX value and Color background of the cell). Is there anyway background colors could be changed several times depending on different results obtained by formula? – Bibbie

Can be done without using Macro? I have a security issue that doesn't allow me to execute Macros in the environment where it should be used – Smoker

Answer 2

In my point of view, the 1st answer is pretty good. I'm not famous a lot with VBA :) But to avoid some issues it's good to start the hex number with # and check is the 1st symbol is #, because we control a range as I see. Then control a full length of value to be equal to 7 symbols and then calculate a colour to set a background.

Cheers!

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 Left(rng.Value2, 1) = "#" And Len(rng.Value2) = 7 Then
            clr = Right(rng.Value2, 6)
            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

Chapel

Answer 3

Slight update to Alexey Ryzhkov's submission. Adding functionality to reset the background to 'None' when the cell is emptied.

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 IsEmpty(rng.Value2) Then
            rng.Interior.Color = xlNone
        ElseIf Trim(rng.Value2) = "" Then
            rng.Interior.Color = xlNone
        ElseIf Left(rng.Value2, 1) = "#" And Len(rng.Value2) = 7 Then
            clr = Right(rng.Value2, 6)
            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

Shandra

If interested, please check out my new question based on your answer. Thanks! #78963460 – Metallo

Answer 4

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.count <> 1 Then Exit Sub
    Target.Interior.Color = CLng("&H" & Target.Value)
End Sub

It works, but for some reason on the basis of BGR, not RGB: ff0000 gives blue and 0000ff gives red :D

Alecto

Nice shortcut on the Hex2Dec. I couldn't for the life of me remember the quick way of doing that. – Hamachi

@Jeeped lol, but see the remark at the end of the answer. It surprised me :p – Alecto

Thanks a lot, will try it. – Bibbie

I have another question: How could it be used on Google Spreadsheet (excel)? I wanted to combine an Excel doc from my desktop with one on Google Spreadsheet... but the code no longer works on Google spreadsheet :( – Bibbie