Fill a cell with color based on it's HEX value in Excel 2013

Question

I have a spreadsheet with cells in the A column containing colors in their HEX format. Is it possible to fill the adjacent cell automatically with the color matching the HEX value?

From the research done until now I understand the VBA should first convert the HEX string to it's RGB correspondent and then fill the cell color with the result.

E.g.: if A1 contains the value "7fcac3" (or "#7fcac3" but I don't think the pound is required), the VBA should fill the adjacent B cell with RGB (127,202,195).

Below is an example of how the VBA might look, found (here). The problem is that I get a "Compile Error: Invalid outside procedure" error in Excel 2013.

For i = 1 To LastRow

Sub SetHexColors()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
Cells(i, "B").Interior.Color = HEXCOL2RGB(Cells(i, "A"))
Next
End Sub

Public Function HEXCOL2RGB(ByVal HexColor As String) As String
Dim Red As String, Green As String, Blue As String
HexColor = Replace(HexColor, "#", "")
Red = Val("&H" & Mid(HexColor, 1, 2))
Green = Val("&H" & Mid(HexColor, 3, 2))
Blue = Val("&H" & Mid(HexColor, 5, 2))
HEXCOL2RGB = RGB(Red, Green, Blue)
End Function

Many thanks, Mathieu

Worked great after I removed the first line and added an If statement to ignore blank rows in between. If Cells(i, "A").Value <> "" Then Cells(i, "B").Interior.Color = HEXCOL2RGB(Cells(i, "A")) End If — trax-cio

Mark Balhoff Mark Balhoff · Accepted Answer · 2014-07-14T18:28:10

The first line of code:

For i = 1 To LastRow

is not in the inside of a Sub or Function. Looks like that is a copy of a line you already have in the Sub SetHexColors so I would expect you just need to comment out or delete that first line. The only lines of code you can put outside Subs and Functions are variable declarations and things like Option statements (e.g., Option Explicit)

Fill a cell with color based on it's HEX value in Excel 2013

1 Answers