Skip Empty Cells or Ones with a Word in Them and Run function

[dj44]

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

[mancubus]

Sub vbax_61215_DisplayHexColors()

Dim i As Long

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
If InStr(Cells(i, 1).Value, "#") > 0 And Cells(i, 1).Value <> "" And Cells(i, 1).Value <> "[skip]" Then
Cells(i, 2).Interior.Color = HEXCOL2RGB(Cells(i, 1))
End If
Next

End Sub

[dj44]

Thank you my friend,

yes that worked nicely.

i made a mini array as well, but im sure it doesnt work. Just in case i wanted to skip more words





Sub testhex()

Dim i As Long


arrWords = Array("10", "20", "30")


For j = LBound(arrWords) To UBound(arrWords)


For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

If InStr(Cells(i, 1).Value, "#") > 0 And Cells(i, 1).Value <> arrWords(j) Then

Cells(i, 2).Interior.Color = HEXCOL2RGB(Cells(i, 1))
End If
Next
Next j




End Sub



be fore i had all these black colors in my empty cells - that wasnt very nice but its all good now

and thank you

[mancubus]

you are welcome
tx for marking the threas as solved