Macro to Color Fill Cell based on Cell Hex Code Value.

johndrew982 · Jan 29th 2022

I am wanting to have columns "A", "B" and "C" (RGB) values converted into hex codes and fill "E" with the associated color of the hex code. Therefore when I change the RGB numbers I can see what color it is. This will allow me to print a color chart of the ones the cheerleaders use or would like to use. This would be for 20 rows....OZGRID COLORS.xlsx on their shirts. rows.

rollis13 · Jan 29th 2022

Found this snippet in my 'desk drawer' and I think it's what you need:

Code

Option Explicit
Sub GenerateColors()
    Dim cell   As Range
    Dim iRGB   As Long
    For Each cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        With Rows(cell.Row).Cells
            iRGB = RGB(.Range("A1").Value, .Range("B1").Value, .Range("C1").Value)
            .Range("D1").Value = "&H" & Right("000000" & Hex(iRGB), 6)
            .Range("E1").Interior.Color = iRGB
        End With
    Next cell
End Sub

Display More

johndrew982 · Jan 30th 2022

It works but when I add to active x control it adds

Private Sub CommandButton1_Click()

End Sub

to the macro and will not work with the control button. I can go back and run the macro by itself and it works. How do I keep it from adding the Private Sub CommandButton?

Logit · Jan 30th 2022

When using the ActiveX button, you need to insert the name of the macro you want to run ... like this :

Code

Private Sub CommandButton1_Click()
     GenerateColors
End Sub

**royUK** · Jan 30th 2022

You can also simply copy the body of the code, like this

Code

Option Explicit
Private Sub CommandButton1_Click()
 Dim cell As Range
 Dim iRGB As Long
 For Each cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
 With Rows(cell.Row).Cells
 iRGB = RGB(.Range("A1").Value, .Range("B1").Value, .Range("C1").Value)
 .Range("D1").Value = "&H" & Right("000000" & Hex(iRGB), 6)
 .Range("E1").Interior.Color = iRGB
 End With
 Next cell
End Sub

Display More

johndrew982 · Jan 30th 2022

Thanks Roy. Obviously you are light years ahead of everyone with your vast knowledge of Excel and VBA. Your Website is awesome!

rollis13 · Jan 30th 2022

My name is rollis13, rollis13 Bond .

Macro to Color Fill Cell based on Cell Hex Code Value.

Participate now!

Share