title: Loop through range of cells and change color if a value is in that range more then 3 times
body: <p>I have one column with different numbers in it. My code is sorting them and shall check how often the same number appears in that column. If one value appears more than 3 times, it should color all rows containing that value, else the rows should be deleted. </p>
  
  <p>Here is my code so far:</p>
  
  <pre><code>Sub mySub10()
  
  Dim wsTEMP As Worksheet
  Dim wsSPECIAL As Worksheet
  Dim wsTEMPLrow As Long
  Dim i As Integer
  Dim x As Integer
  Dim rng As Range
  
  Set wsTEMP = ThisWorkbook.Sheets("Temp")
  Set wsSPECIAL = ThisWorkbook.Sheets("Spezial")
  
  Application.ScreenUpdating = False
  
  wsTEMPLrow = Worksheets("Temp").Range("A" &amp; Worksheets("Temp").Rows.Count).End(xlUp).Row
  
  With wsTEMP
  
    .Columns("A:Q").Sort Key1:=.Range("L1"), Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal
  
  For i = wsTEMPLrow To 5 Step -1
      Set rng = Range("A" &amp; i)
      If Cells(i, 12).Value = Cells(i - 1, 12).Value And Cells(i, 12).Value = Cells(i - 2, 12).Value And Cells(i, 12).Value = Cells(i - 3, 12).Value And Cells(i, 12).Value = Cells(i - 4, 12).Value Then
              Range("A" &amp; i).EntireRow.Interior.ColorIndex = 6
              Range("A" &amp; i - 1).EntireRow.Interior.ColorIndex = 6
      End If
  Next
  
  For i = wsTEMPLrow To 2 Step -1
      Set rng = Range("A" &amp; i)
      If rng.Interior.ColorIndex &lt;&gt; 6 Then
          rng.EntireRow.Delete
      End If
  Next        
  
  End With
  
  End Sub
  </code></pre>