dj44
10-31-2017, 01:07 PM
folks,
good day,
i would like to skip cells that are empty or have the word "[skip] in them
and do the rest colors.
Column A my hexes and Column B the display
#00B0F0
#9932CC
< blank cell
#ffffff
#ffa500
[skip]
#ffffff
Sub DisplayHexColors()
Dim i, LastRow
Dim oCell As Range
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
' If InStr(1, Cells.Value, "#") > 0 Then ' to try and avoid cells that dont have the # in them?
Cells(i, "B").Interior.Color = HEXCOL2RGB(Cells(i, "A"))
'End If
Next
End Sub
Public Function HEXCOL2RGB(ByVal HexColor As String) As Long
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
please can you advise on the best way to do this as i m not sure
good day,
i would like to skip cells that are empty or have the word "[skip] in them
and do the rest colors.
Column A my hexes and Column B the display
#00B0F0
#9932CC
< blank cell
#ffffff
#ffa500
[skip]
#ffffff
Sub DisplayHexColors()
Dim i, LastRow
Dim oCell As Range
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
' If InStr(1, Cells.Value, "#") > 0 Then ' to try and avoid cells that dont have the # in them?
Cells(i, "B").Interior.Color = HEXCOL2RGB(Cells(i, "A"))
'End If
Next
End Sub
Public Function HEXCOL2RGB(ByVal HexColor As String) As Long
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
please can you advise on the best way to do this as i m not sure