title: MS Excel select rows based on font color
body: <p>I had a big amount of data about employees contract, a macro had created to auto change the font color to "RED" color for outdated employees rows and MsgBox pop-up to alert user amount of outdated data.</p>
  
  <p>Below are the coding.</p>
  
  <pre><code>Sub Worksheet_Activate()
  
    Dim startCell As Integer, endCell As Integer
    Dim column As Integer
    Dim CountCells As Integer
    Dim x As Integer
  
    With Worksheets("Sheet1")
  
    lastrow = Range("L1048576").End(xlUp).Row
  
    CountCells = 0
  
    For i = 4 To lastrow
  
        If Range("L" &amp; i).Value &lt;&gt; "" And Now &lt;&gt; "" Then
  
            If Range("L" &amp; i).Value &lt;= Now Then
  
                Range("L" &amp; i).Font.ColorIndex = 3
  
                    If Range("L" &amp; i).Font.ColorIndex = 3 Then
  
                       CountCells = CountCells + 1
  
                    End If
            End If
        End If
    Next i
  
       MsgBox CountCells &amp; " expiring"
  
    End With
  End Sub
  </code></pre>
  
  <p>Now i need the Excel to auto select each of them by a single click of button, problem facing was:</p>
  
  <ul>
  <li><p>which button should be use? Form Control button or ActiveX control button? </p></li>
  <li><p>3 button are needed, (1) Auto select (2) copy and paste (3) delete each selected row.</p></li>
  <li>how to write code for each of the button?</li>
  <li>after user click the (1) button, it will auto select each RED data.</li>
  <li>then by pressing (2) button, it will copy them to new worksheet.</li>
  <li>lastly when user press the (3) button, each selected rows will be delete because only deleted each of them only the remain data will replacing the blank space. </li>
  </ul>