title: Speed up Excel VBA search script
body: <p>I need to search for duplicate values and mark them in an Excel spreadsheet. I have my data to verify in column <code>D</code> and the data where possible duplicates are in column <code>K</code>. I need to check for each row in column <code>D</code> all the rows in col. <code>K</code>.</p>
  
  <p>This is my current script for this:</p>
  
  <pre><code>Sub MySub()
  Dim ThisCell1 As Range
  Dim ThisCell2 As Range
      For Each ThisCell1 In Range("D1:D40000")
      'This is the range of cells to check
          For Each ThisCell2 In Range("K1:K40000")
          'This is the range of cells to compare
              If ThisCell1.Value = ThisCell2.Value Then
              If ThisCell1.Value &lt;&gt; "" Then
                  ThisCell1.Interior.ColorIndex = 3
                  End If
                  Exit For
                  End If
              Next ThisCell2
          Next ThisCell1
  End Sub
  </code></pre>
  
  <p>The problem with this is that it's <em>VERY</em> slow. I mean it takes <em>hours</em> to check the data which is not acceptable. Even when the range is set to <code>1:5000</code>, it still takes 10-15 minutes to finish. Is there any way to make it faster?</p>