How to highlight a cell using the hex color value within the cell?
Solution 1
Can't be achieved with Conditional Formatting for all colours.
Assuming: Row1 contains Data Labels, data set does not have gaps, the HEX colour is for the fill not the font, you have parsed the HEX colour values (numbers, not formulae) into Columns C:E (R,G,B) and that you do not require to do this often, then the ColourCells macro might suit:
Sub ColourCells()
Dim HowMany As Integer
On Error Resume Next
Application.DisplayAlerts = False
HowMany = Application.InputBox _
(Prompt:="Enter last row number.", Title:="To apply to how many rows?", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True
If HowMany = 0 Then
Exit Sub
Else
Dim i As Integer
For i = 2 To HowMany
Cells(i, 3).Interior.Color = RGB(Cells(i, 3), Cells(i, 4), Cells(i, 5))
Next i
End If
End Sub
and enter the value you want for n when prompted.
Sample output and formulae etc:
Excel's RGB() function actually creates a BGR value (I don't think anybody that might know why is saying why though) so Excel shows nibbles in reverse order. For the code Columns3,4,5 was logical but BGR rather than the conventional RGB in the image I thought might look odd. For F in the image the C3 value (the LEFT hand column of the 'RGB' three) is derived from applying RIGHT() to the Hex colour.
Solution 2
Minor edit to Jon Peltier's answer. His function ALMOST works, but the colors it renders are incorrect due to the fact the Excel will render as BGR rather than RGB. Here is the corrected function, which swaps the pairs of Hex values into the 'correct' order:
Sub ColorCellsByHex()
Dim rSelection As Range, rCell As Range, tHex As String
If TypeName(Selection) = "Range" Then
Set rSelection = Selection
For Each rCell In rSelection
tHex = Mid(rCell.Text, 6, 2) & Mid(rCell.Text, 4, 2) & Mid(rCell.Text, 2, 2)
rCell.Interior.Color = WorksheetFunction.Hex2Dec(tHex)
Next
End If
End Sub
Solution 3
Much simpler:
ActiveCell.Interior.Color = WorksheetFunction.Hex2Dec(Mid$(ActiveCell.Text, 2))
Mid strips off the leading "#", Hex2Dec turns the hex number into a decimal value that VBA can use.
So select the range to process, and run this:
Sub ColorCellsByHexInCells()
Dim rSelection As Range, rCell As Range
If TypeName(Selection) = "Range" Then
Set rSelection = Selection
For Each rCell In rSelection
rCell.Interior.Color = WorksheetFunction.Hex2Dec(Mid$(rCell.Text, 2))
Next
End If
End Sub
Solution 4
This is another option - it updates the cell color when you select the cell assuming the value in the cell starts with "#" and is 7 characters.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Left(ActiveCell.Text, 1) = "#" And Len(ActiveCell.Text) = 7) Then
ActiveCell.Interior.Color = WorksheetFunction.Hex2Dec(Mid$(ActiveCell.Text, 2))
End If
End Sub
Solution 5
There is no need to repeatedly pierce the VBA/Worksheet barrier to convert. This streamlined version gets the byte order correct:
Sub ColorCellsByHex()
Dim r
If TypeName(Selection) <> "Range" Then Exit Sub
For Each r In Selection
r.Interior.Color = Abs(("&H" & Mid(r, 6, 2) & Mid(r, 4, 2) & Mid(r, 2, 2)))
Next
End Sub
knowbody
Updated on July 09, 2022Comments
-
knowbody almost 2 years
I have a spreadsheet of symbols and matching hex colors. I want to fill the cell itself (or the one next to it) with the hex color within the cell. I've read a bit about "conditional formatting", and I think that's the way to do it.
How might I achieve the result I would like?
-
ryyker almost 10 yearsCan see where this might be used to illustrate temperature data in a much more appealing way then just tabular data. +1
-
snh_nl almost 7 yearsCan this function be changed so it can be used straight into condititonal formatting? IfHexColour() -- so it detects a # as first element, then validates 6 chars, then converts to RGB and sets colour - to the active cell
-
Jon Peltier almost 7 yearsI don't see how built-in conditional formatting could handle this. But you could use VBA to detect a string beginning with #, validate it as a valid hex string, and color the cell accordingly.
-
Killer over 6 yearsSo much better! Just select the range and execute the macro. Thats a real good answer. Thanks a lot.
-
Stefan de Kok about 6 yearsif you want the color code to also be hidden, add the follow line just before
Next
:rCell.Font.Color = WorksheetFunction.Hex2Dec(tHex)
-
GenDemo over 3 yearsI presume from all these comments, that VBA doesn't have a HEX colour function? hence it needs to be converted to RGB?