title: Sorting rows in a range with specific background colour in Excel using vba
body: <p>I'm trying to sort a range of rows in an Excel sheet which all start with a specific green background colour in the first column, but my vba code does not do it at all and I can't see why. The objective is as an example to get from this:</p>
  <p><a href="https://i.stack.imgur.com/15fJj.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/15fJj.jpg" alt="enter image description here" /></a>
  to this:
  <a href="https://i.stack.imgur.com/QsJji.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QsJji.jpg" alt="enter image description here" /></a></p>
  <pre><code>Private Sub Sort_Click()
      
      Dim StartRow, EndRow, i As Integer
      Dim row As Range, cell As Range
      
      'Discover the data starting and end rows
      i = 1
      StartRow = 1
      EndRow = 1
  
      'Check the first cell of each row for the start of background colour
      For Each row In ActiveSheet.UsedRange.Rows
          Set cell = Cells(row.row, 1)
          If i &lt; 3 Then
              If Hex(cell.Interior.Color) = &quot;47AD70&quot; And i = 1 Then
                  StartRow = row.row
                  i = 2
              ElseIf Hex(cell.Interior.Color) &lt;&gt; &quot;47AD70&quot; And i = 2 Then
                  EndRow = row.row - 1
                  i = 3
              End If
          End If
      Next row
      
      'Sort the range
      Range(&quot;A&quot; &amp; StartRow &amp; &quot;:&quot; &amp; &quot;A&quot; &amp; EndRow).Sort Key1:=Range(&quot;A&quot; &amp; StartRow &amp; &quot;:&quot; &amp; &quot;A&quot; &amp; EndRow), Order1:=xlAscending, Header:=xlNo
  
  End Sub
  </code></pre>
  <p>The code should check the first cell of each row in Column &quot;A&quot; until it reaches the first green backgroend colour where it assigns that row number to the variable StartRow. The loop continues until it no longer detects the green background colour in the first cell. It then assigns that row number - 1 to the variable EndRow. At the end, it sorts the green range numerically using StartRow and EndRow as the range.
  Possibly, The Range statement part is not working correctly. I wonder if someone could help with a resolution or a better code all together. The images demonstrate the rows in the green range sorted manually. Thanks in advance</p>