title: search entire Excel workbook for text string and highlight cell
body: <p>I need to search an entire Excel <code>workbook</code> containing multiple sheets for
  text strings that may be present in multiple columns 
  (say in the range column A to column J)</p>
  
  <p>When a text string is found, it applies a color format to the cell.</p>
  
  <p>Is this possible, or will I have to make a rule for each sheet?</p>
  
  <p>An example:</p>
  
  <ul>
  <li>Find string "information" anywhere in my <code>workbook</code> and format cell blue</li>
  </ul>
  
  <p>I have multiple different text strings to enter and each will have a different color format.<br>
  Is there a way to combine them all in one rule, or will this just need to have me create a new rule for each, using the same rule modified for each text string?</p>
  
  <p>I'm really new at the conditional formatting in Excel, so if you could be gentle and walk me through each step that would be appreciated.</p>
  
  <p>I have searched the oracle internet and found this solution.  I need to test it but it may do what I need.</p>
  
  <p>This requires me to build a table called ChooseColors. The first column is the search string, the second is the colors -- pick from the available range. 
  The search area is on a second sheet -- start on this sheet. </p>
  
  <p>Code:</p>
  
  <pre><code>Sub DoColors()
  Dim Picker As Variant
  Dim Colors As Variant
  Dim Rws As Long, j As Long
  Dim i As Integer
  Dim Sht As String
  Dim c As Range
  Dim FirstAddress
  
  Sht = ActiveSheet.Name
  'load search strings and colors into arrays
  Application.Goto Reference:="ChooseColors"
  ReDim Picker(1 To Selection.Rows.Count)
  ReDim Colors(1 To Selection.Rows.Count)
  For i = 1 To Selection.Rows.Count
  Picker(i) = ActiveCell.Value
  Colors(i) = ActiveCell.Offset(0, 1).Interior.ColorIndex
  ActiveCell.Offset(1, 0).Select
  Next i
  'search the test range, changing backgrounds as required
  Sheets(Sht).Activate
  For i = 1 To UBound(Picker)
  With Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
      Set c = .Find(Picker(i), LookIn:=xlValues)
      If Not c Is Nothing Then
          FirstAddress = c.Address
          Do
              c.Interior.ColorIndex = Colors(i)
              Set c = .FindNext(c)
          Loop While Not c Is Nothing And c.Address &lt;&gt; FirstAddress
      End If
  End With
  Next i
  
  End Sub
  </code></pre>