CODEWITHSUNDEEP
excelvbaloopssorting
Bob_F
Bob_F

Reputation: 43

Sorting rows in a range with specific background colour in Excel using vba

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:

enter image description here to this: enter image description here

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 < 3 Then
            If Hex(cell.Interior.Color) = "47AD70" And i = 1 Then
                StartRow = row.row
                i = 2
            ElseIf Hex(cell.Interior.Color) <> "47AD70" And i = 2 Then
                EndRow = row.row - 1
                i = 3
            End If
        End If
    Next row
    
    'Sort the range
    Range("A" & StartRow & ":" & "A" & EndRow).Sort Key1:=Range("A" & StartRow & ":" & "A" & EndRow), Order1:=xlAscending, Header:=xlNo

End Sub

The code should check the first cell of each row in Column "A" 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

Upvotes: 0

Views: 521

Answers (2)

Bob_F
Bob_F

Reputation: 43

Well, I may have been a bit silly on this issue here, however after some more reading it turned out that to sort complete rows rather than column A only, all I simply had to do was to actually specify whole rows rather than a single column, in the sorting part of the code! And that is dpne by replacing the line:

Range("A" & StartRow & ":" & "A" & EndRow).Sort Key1:=Range("A" & StartRow & ":" & "A" & EndRow), Order1:=xlAscending, Header:=xlNo

with:

Range("A" & StartRow & ":" & "D" & EndRow).Sort Key1:=Range("A" & StartRow & ":" & "A" & EndRow), Order1:=xlAscending, Header:=xlNo

All that's happened above is that the "A" in the range section has changed to "D" to cover all used columns for sorting the rows.

Upvotes: 0

JohnyL
JohnyL

Reputation: 7132

You need to use last parameter of Find method SearchFormat. Set it to whatever format you need:

Sub FGG()
    Dim rng As Range, rngStart As Range, rngEnd As Range
    '// Clear previous format, if any
    Application.FindFormat.Clear
    '// Set search format
    Application.FindFormat.Interior.Color = Hex("47AD70")
    '// Find first cell with format
    Set rngStart = Range("A:A").Find(What:="*", SearchFormat:=True)
    '// Find last cell with format by using xlPrevious
    Set rngEnd = Range("A:A").Find(What:="*", SearchDirection:=xlPrevious, SearchFormat:=True)
    '// Define final range
    Set rng = Range(rngStart, rngEnd)
    '// Sort range and say that that the range has no header
    rng.Sort Key1:=rng(1), Header:=xlNo
End Sub

Upvotes: 0

Related Questions