title: Excel VBA Highlight duplicates in active column
body: <p>I'm trying to create a macro that will highlight duplicates in the column where text is being entered. </p>
  
  <p>I have 54 columns and want to highlight duplicates in each column as the text is entered. The scenario is: if "STAPLES" is entered twice in column B then the cells (B3, B22) would be highlighted. I want a macro that can do this for each column, so if "STAPLES" is entered into column E only once nothing should happen.</p>
  
  <p>Using the Conditional Formatting =COUNTIF doesn't necessarily help (due to the workflow of copying columns to new worksheets). </p>
  
  <p>I have this macro already:</p>
  
  <pre><code>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  
  Dim Rng As Range
  Dim cel As Range
  
  'Test for duplicates in a single column
  'Duplicates will be highlighted in red
  
  
  Set Rng = Range(Range("C1"), Range("C" &amp; Rows.Count).End(xlUp))
  
  
  For Each cel In Rng
      If WorksheetFunction.CountIf(Rng, cel.Value) &gt; 1 Then
  
  cel.Interior.ColorIndex = 3
      End If
  Next cel
  
  End Sub
  </code></pre>
  
  <p>It works ok but is only for one column ("C"). </p>
  
  <p>How do I set the range to be the active column? </p>
  
  <p>I have tried to change Rng to </p>
  
  <pre><code>'Set Rng = Range(ActiveCell,ActiveCell.Column.End(xlUp)) 
  </code></pre>
  
  <p>but this is obviously wrong. </p>
  
  <p>Any ideas?</p>