hex to color

[manbaby]

I am trying to convert a long list of hexcodes into their corresponding colors on excel. Is there any function of some sort to do this?

[Paul_Hossler]

Not sure what / mow much you're looking to do, but here's something I put together from examples on the web


28867



Otherwise, the WS function DEC2HEX() will convert a Long color value to a hex String

[manbaby]

I am trying to convert the hex string into a cell color if that is possible

[manbaby]

I know it can be done manually with the filling the cell with the hex but i am trying to do this for 1600 rows so if there is a function that would be great

[Paul_Hossler]

If this is a one time conversion, I'd use a Sub

Select the cells with the color numbers (I'm assuming that these are RGB Longs and not ColorIndex's) and run this little sub

It puts the Hex into the cell to the right, but you can changer that




Option Explicit


Sub ColorToHex()
Dim r As Range

If Not TypeOf Selection Is Range Then Exit Sub

For Each r In Selection.Cells
With r
If Len(.Value) > 0 Then
On Error Resume Next
.Offset(0, 1).NumberFormat = "@"
.Offset(0, 1).Value = Right("000000" & Application.WorksheetFunction.Dec2Hex(.Value), 6)
On Error GoTo 0
End If
End With
Next


End Sub




Sheet1 has a little demo

[arnelgp]

' https://exceloffthegrid.com/convert-color-codes/
Function GetLongFromHex(ByVal hexColor As String) As Long


Dim r As String
Dim G As String
Dim B As String


hexColor = VBA.Replace(hexColor, "#", "")
hexColor = VBA.Right$("000000" & hexColor, 6)


r = Left(hexColor, 2)
G = Mid(hexColor, 3, 2)
B = Right(hexColor, 2)




GetLongFromHex = Application.WorksheetFunction.Hex2Dec(B & G & r)


End Function




Function GetHexFromLong(ByVal longColor As Long) As String


Dim r As String
Dim G As String
Dim B As String


r = Format(Application.WorksheetFunction.Dec2Hex(longColor Mod 256), "00")
G = Format(Application.WorksheetFunction.Dec2Hex((longColor \ 256) Mod 256), "00")
B = Format(Application.WorksheetFunction.Dec2Hex((longColor \ 65536) Mod 256), "00")

GetHexFromLong = "#" & r & G & B


End Function
'end of code - https://exceloffthegrid.com/convert-color-codes/

[snb]

@manbaby

Don't be childish and post a sample workbook in which you indicate what result you are after.

[arnelgp]

see this sample excel file
https://www.dropbox.com/s/fog8lr7ur8pqvo4/Hex%20Color.xlsm?dl=0

[snb]

Posting here in this forum, not elsewhere.

[arnelgp]

i cannot post here, my browser (Edge and Chrome) won't allow me
to attach anything on this site.
the site is Tag as "Not Secured".