title: Highlight Rows in Excel Using VBA Code to Find Cells With Values Ending in Particular Terms
body: <p>I have an Excel document with lists of contact information that was typed by hand from paper contact sheets. As such, there are many typos (human error). Because the lists are so long, I don't have time to look through the lists by hand to correct obvious errors. The most important piece of contact information for each row is the email address, which if it's spelled wrong, I have no way of contacting the person.</p>
  
  <p>I used a macro from a different user's answer to highlight rows containing common errors in the domain name portion of the email address, such as "@gamil.com", ".copm", ".cpm", "@hormail.com", "@yahool.com", "@yaho.com", "fmail.com", "@vergin.net".</p>
  
  <p>(To give credit where credit is due, the code I started with came from user <a href="https://stackoverflow.com/users/2140173/me-how">me how</a> who is amazing at macros, and I wish I had a way to ask him directly to help with this, but I don't have the "reputation" to make comments yet. The question that I found this code as an answer to was <a href="https://stackoverflow.com/questions/19251379/finding-multiple-text-in-excel">Finding Multiple text in Excel</a>.)</p>
  
  <pre><code>Sub HighlightRowsBasedOnArrayCondition()
  Dim searchTerms As Variant
  searchTerms = Array("@gamil.com", ".copm", ".cpm", "@hormail.com", "@yahool.com", "@yaho.com", "fmail.com", "@vergin.net")
  
  ReDim rowsToHighlight(0) As String
  
  Dim allRange As Range
  Set allRange = ActiveSheet.UsedRange
  
  Dim cell As Range, word As Variant
  For Each cell In allRange
  For Each word In searchTerms
  If InStr(1, cell, word, vbTextCompare) Then
  rowsToHighlight(UBound(rowsToHighlight)) = CStr(cell.Row)
  ReDim Preserve rowsToHighlight(UBound(rowsToHighlight) + 1)
  End If
  Next word
  Next cell
  ReDim Preserve rowsToHighlight(UBound(rowsToHighlight) - 1)
  
  Dim v As Long
  For v = UBound(rowsToHighlight) To LBound(rowsToHighlight) Step -1
  Rows(rowsToHighlight(v)).Interior.Color = RGB(255, 0, 0)
  Next
  End Sub
  </code></pre>
  
  <p><strong>I would like to better specify these terms as being the end of the cell value</strong> so that I can then have the macro highlight email addresses that end in "@yahoo.co" and "@gmail.co" so that I can catch it when the ".com" is incomplete. Obviously, if I were to use the current version of the macro with these terms, it will highlight not only those email errors that I'm looking for but also those with the completed domain name. <strong>Can someone please tell me if it is possible to specify that the search terms are the ending of the cell values? If so, can you please guide me as to how to alter this code to do so?</strong></p>