Highlight a cell / VBA

[gripper]

I am trying to create a filtering code to highlight cells if a particular word is in the string of that cell.

I found this code that allows you to highlight the column and then run the macro and it will change the font color of the actual word matched; not the entire string. I would like to change this to highlight the cell in yellow if within that cell it has a match within the string.

I am wondering is it possible instead of adding each word to the VBA code to instead reference a text file or another excel workbook with the words that need filtering. I have about 60 words I need to screen on each excel sheet I open and run the macro and since these words are updated, added or deleted it may be easier just to have the info in a central location.

Here is the code

Public Sub HighlightCodes()
        ' Select Cells to be highlighted and Run this Sub.
         Dim Codes(1 To 8)
         Dim Rng As Range
         Dim i As Long
         Dim StartPos As Long
               Codes(1) = "car."
               Codes(2) = "motorcycle"
               Codes(3) = "scooter"
               Codes(4) = "airplane"
               Codes(5) = "skateboard"
               Codes(6) = "bicycle"
               Codes(7) = "motorhome"
               Codes(8) = "boat"

         For Each Rng In Selection.Cells
               For i = 1 To 8
         StartPos = InStr(Rng.Value, Codes(i))
         If StartPos > 0 Then Rng.Characters(StartPos, Len(Codes(i))).Font.ColorIndex = 15
      Next i
   Next Rng
End Sub

 

[John Davis]

I am trying to create a filtering code to highlight cells if a particular word is in the string of that cell.

I found this code that allows you to highlight the column and then run the macro and it will change the font color of the actual word matched; not the entire string. I would like to change this to highlight the cell in yellow if within that cell it has a match within the string.

I am wondering is it possible instead of adding each word to the VBA code to instead reference a text file or another excel workbook with the words that need filtering. I have about 60 words I need to screen on each excel sheet I open and run the macro and since these words are updated, added or deleted it may be easier just to have the info in a central location.

Here is the code

Public Sub HighlightCodes()
        ' Select Cells to be highlighted and Run this Sub.
         Dim Codes(1 To 8)
         Dim Rng As Range
         Dim i As Long
         Dim StartPos As Long
               Codes(1) = "car."
               Codes(2) = "motorcycle"
               Codes(3) = "scooter"
               Codes(4) = "airplane"
               Codes(5) = "skateboard"
               Codes(6) = "bicycle"
               Codes(7) = "motorhome"
               Codes(8) = "boat"

         For Each Rng In Selection.Cells
               For i = 1 To 8
         StartPos = InStr(Rng.Value, Codes(i))
         If StartPos > 0 Then Rng.Characters(StartPos, Len(Codes(i))).Font.ColorIndex = 15
      Next i
   Next Rng
End Sub

For the first question, maybe:

Public Sub HighlightCodes()
        ' Select Cells to be highlighted and Run this Sub.
         Dim Codes(1 To 8)
         Dim Rng As Range
         Dim i As Long
         Dim StartPos As Long
               Codes(1) = "car."
               Codes(2) = "motorcycle"
               Codes(3) = "scooter"
               Codes(4) = "airplane"
               Codes(5) = "skateboard"
               Codes(6) = "bicycle"
               Codes(7) = "motorhome"
               Codes(8) = "boat"

         For Each Rng In Selection.Cells
               For i = 1 To 8
                    StartPos = InStr(Rng.Value, Codes(i))
                       If StartPos > 0 Then Rng.Interior.ColorIndex = 6
               Next i
         Next Rng

End Sub