Excel VBA 55 ExcelでA列の16進数の色でB列を塗りつぶす

A列の16進数の色でB列を塗りつぶします。

Sub FillColorFromHex()

Dim ws As Worksheet

Dim lastRow As Long

Dim i As Long

Dim hexColor As String

Dim r As Long, g As Long, b As Long

Set ws = ActiveSheet

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow

hexColor = Trim(ws.Cells(i, "A").Value)

' #を除去

If Left(hexColor, 1) = "#" Then

hexColor = Mid(hexColor, 2)

End If

' 6桁の16進数のみ処理

If Len(hexColor) = 6 Then

r = CLng("&H" & Mid(hexColor, 1, 2))

g = CLng("&H" & Mid(hexColor, 3, 2))

b = CLng("&H" & Mid(hexColor, 5, 2))

ws.Cells(i, "B").Interior.Color = RGB(r, g, b)

End If

Next i

End Sub

A列に16進数を記述します。

マクロ実行後はこんな感じです。