title: look for cells which contain certain value in another sheet
body: <p>I have two sheets where Sheet 1 has many account names in column C and Sheet 2 has a list of account names in column B.</p>
  
  <p>I would like to search in Sheet 1 column C all cells where the value contains the cell value in Sheet 2 column B and high light them in yellow.</p>
  
  <p>Below are my codes, with with I keep getting error message "Objectiv Variable or With Block variable not set" or "Subsript without range".</p>
  
  <pre><code>Sub search_name() ' ' search_name Macro
      Dim a As Range
      Set a = Sheets("NAMES").Range("B1")
      For Each a In Sheets("NAMES").Range("B1:B88")
        Sheets("MASTER").Select
        Columns("C:C").Select
        Selection.Find(a, After:=ActiveCell, LookIn:=xlFormulas, _
          LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False).Activate
        Application.CutCopyMode = False
        With ActiveCell.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .Color = 65535
          .TintAndShade = 0
          .PatternTintAndShade = 0
        End With
      Next a
  End Sub
  </code></pre>