title: vba test cells in columns for only listed words in any order with commas
body: <p>im trying to run test on columns for list of words in any order with commas included. i.e mars,earth, moon, sun  test only these words are in e columns if not error msg. could commas ie mars,sun or moon,sun my code is below im having issue with words that have commas and in differnt order pls help</p>
  <pre><code>Sub substring()
  
  Dim Target As String, c As Range, lr As Long, ws As Worksheet
  
  Dim Target2 As String
  
  Dim Target3 As String
  
  Dim Target4 As String
  
   
  
  Set ws = ThisWorkbook.Sheets(&quot;Sheet6&quot;)
  
  lr = ws.Range(&quot;A&quot; &amp; ws.Rows.Count).End(xlUp).Row
  
  Target = &quot;sun&quot;
  
  Target2 = &quot;moon&quot;
  
  Target3 = &quot;earth&quot;
  
  Target4 = &quot;mars&quot;
  
  For Each c In Range(&quot;A2:B&quot; &amp; lr)
  
      If StrComp(c.Text, Target, vbTextCompare) And StrComp(c.Text, Target2, vbTextCompare) And StrComp(c.Text, Target3, vbTextCompare) And StrComp(c.Text, Target4, vbTextCompare) Then
  
          MsgBox c.Address
  
     End If
  Next c
  
  End Sub
  
   
  </code></pre>