title: Searching for string anywhere in a cell - Excel VBA
body: <p>I've got a macro that runs through a list of search words, concatenates them into a string separated by <code>|</code>s and then searches a separate sheet for matches. However this macro only finds exact matches (down to the sentence case) and not just if the string appears in the cell. I think I should be using the <code>FoundCell</code> function (instead of <code>If InStr...</code>) to get the searching I want, but I'm having trouble.</p>
  
  <pre><code>Public Sub HighlightListedValues()
      Dim strConcatList As String
      Dim cell As Range
  
      For Each cell In Sheets("List").Range("A1:A13")
          strConcatList = strConcatList &amp; cell.Value &amp; "|"
      Next cell
  
      For Each cell In Intersect(Sheets("Sheet1").Range("A:A"), Sheets("Sheet1").UsedRange)
          If InStr(strConcatList, cell.Value) &gt; 0 Then       
              cell.EntireRow.Interior.Color = RGB(255, 0, 0) 
          End If
      Next cell
  End Sub
  </code></pre>