print HEX code as color in excel sheet

[annarizzi]

Hello I have searched and attempted to implement many various macros to do what I am looking for, but I have not been successful. Could anyone give me an excel sheet that has the macro already setup for doing the following.
I have a list of HEX codes I am using for a bar plot in R
I want to put the list into excel and then have the color of the HEX code print into a box in excel (so it will end up looking like a column of different colors based on my hex code). Then I want to name each color with the name that corresponds to my bar plot in R. So for instance, if I have a bar plot of the following 5 colors I am basically using excel to make the legend. So then instead of seeing the hex code, I would love to have the cell just filled with the appropriate color <pink> in the cell next to the organism name....
#803c5b <pink> organismA
#d8c2b8 <salmon> organismB
#7e4043 <red> organismC
#dbbed1 <pink> organismD
#6f4940 organismE
#6e475b organismF

Thank you so much for your help with this matter, I really don't understand macros....I can't get them to work.
Cheers
Anna

 

[Trevor G]

Welcome to the Forum Anna,

In Excel there is a formula called HEX2DEC once I striped the # away from your code I could then use the following formula to determine the RGB Red Green Blue numbers. See the screen shot. But you can also use Format Cells and the Fill Tab, Click More Fill, then Custom and you can type in the Hex at the bottom and it will give you the Colours as well as the RGB numbers. You could also then apply Conditional Formatting so when a HEX number is used it colours the cell automatically for you.

=HEX2DEC(LEFT(C3,2))&"-"&HEX2DEC(MID(C3,3,2))&"-"&HEX2DEC(RIGHT(C3,2))

 

[annarizzi]

I tried pasting your formula into excel and it didn't work...sorry I am not sure what I am doing wrong.

Welcome to the Forum Anna,

In Excel there is a formula called HEX2DEC once I striped the # away from your code I could then use the following formula to determine the RGB Red Green Blue numbers. See the screen shot. But you can also use Format Cells and the Fill Tab, Click More Fill, then Custom and you can type in the Hex at the bottom and it will give you the Colours as well as the RGB numbers. You could also then apply Conditional Formatting so when a HEX number is used it colours the cell automatically for you.

=HEX2DEC(LEFT(C3,2))&"-"&HEX2DEC(MID(C3,3,2))&"-"&HEX2DEC(RIGHT(C3,2))

 

[annarizzi]

Hi Trevor,
So your equation works to turn my HEX code into a RGB code, but what I don't understand is how to get the excel cells to color based on that code, I don't want to do it manually I want to format them to color according to either the HEX code or RGB code...

thanks in advance
Anna

I tried pasting your formula into excel and it didn't work...sorry I am not sure what I am doing wrong.

Welcome to the Forum Anna,

In Excel there is a formula called HEX2DEC once I striped the # away from your code I could then use the following formula to determine the RGB Red Green Blue numbers. See the screen shot. But you can also use Format Cells and the Fill Tab, Click More Fill, then Custom and you can type in the Hex at the bottom and it will give you the Colours as well as the RGB numbers. You could also then apply Conditional Formatting so when a HEX number is used it colours the cell automatically for you.

=HEX2DEC(LEFT(C3,2))&"-"&HEX2DEC(MID(C3,3,2))&"-"&HEX2DEC(RIGHT(C3,2))

 

[Trevor G]

Hi Anna,

Which Column will you be using with these words?

organismA
organismB
organismC
organismD
organismE
organismF

 

[annarizzi]

Hi so I just can't get the color to print, your equation is turning the HEX into RGB. I don't care what column anything is in, right now my hex is in the first col1, then your code for RGB is in col2, then I want the color to appear somewhere...Then my organism name can be in col 4.
I have followed this instruction "But you can also use Format Cells and the Fill Tab, Click More Fill, then Custom" BUT I don't have custom available it doesn't look like...so I couldn't finish your suggestion.
Also I don't want to do this manually I have 1000 colors I would like to just print and view in order in excel so I would like to have a quick drag down way to format them...
cheers
Anna

Hi Anna,

Which Column will you be using with these words?

organismA
organismB
organismC
organismD
organismE
organismF

 

[Trevor G]

I have just gone through formatting any cell in a column if it meets your conditions (Words) and it all works no problem. So I have used Conditional Formatting do to this.

Highlight a column and then select Home and Conditional Formatting >>> use Highlight Cells >>> Equal To Text. Type in organismA then click the Drop down and select Custom Format and as before go to the Fill Tab and from there click More Colours you can add in the Hex numbers are the bottom or use the RGB ones from my image. Then Repeat for the others and it will work.

 

[annarizzi]

It sounds like you are manually changing the hex codes in the color area to do this, as I mentioned above I have 1000's of colors, so I don't want to do anything manually, I wanted to be able to do this with a MACRO or something like that.
Anyway thanks for your help, maybe what I want isn't possible...in which case I will go to R or something else.

I have just gone through formatting any cell in a column if it meets your conditions (Words) and it all works no problem. So I have used Conditional Formatting do to this.

Highlight a column and then select Home and Conditional Formatting >>> use Highlight Cells >>> Equal To Text. Type in organismA then click the Drop down and select Custom Format and as before go to the Fill Tab and from there click More Colours you can add in the Hex numbers are the bottom or use the RGB ones from my image. Then Repeat for the others and it will work.

 

[Trevor G]

Please try this in a copy of the workbook

I have created the following code behind the worksheet change event and when you enter one of your organism names it will change the background to match your Hex colours. To see if it is what you need right click the Sheet and then select View Code. Copy the code in. Note in the code the range is A1:IV60000.

Private Sub Worksheet_Change(ByVal target As Range)
Dim intersection As Range
Set intersection = Intersect(target, Range("A1:IV60000"))
'Change cell interior colour if they contain to Hex colour = equivalent RGB
'organismA = 803c5b
'organismB = d8c2b8
'organismC = 7e4043
'organismD = dbbed1
'organismE = 6f4940
'organismF = 6e475b
If Not intersection Is Nothing Then
Dim cell As Range
Application.EnableEvents = False
For Each cell In intersection
Select Case cell
Case "organismA"
cell.Interior.Color = RGB(128, 60, 91)
Case "organismB"
cell.Interior.Color = RGB(216, 194, 184)
Case "organismC"
cell.Interior.Color = RGB(126, 64, 67)
Case "organismD"
cell.Interior.Color = RGB(219, 190, 209)
Case "organismE"
cell.Interior.Color = RGB(111, 73, 64)
Case "organismF"
cell.Interior.Color = RGB(110, 71, 90)
Case Else
cell.Interior.ColorIndex = xlNone
End Select
Next cell
Application.EnableEvents = True
End If
End Sub