Fill a cell with color based on it's HEX value in Excel 2013
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)
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on June 07, 2022Comments
-
Admin about 2 years
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
-
Admin almost 10 yearsNow it's obvious from the author's instructions as well, thanks for pointing that out :)