VB.NET - How to sort a Datagridview by color

Question

I have a Datagridview, where I do a document review. Datagridview is filled by a file, I use the OpenFileDialog to select the file and fill in Datagridview. Then I call a function where a critique is made by the CNPJ/CPF field, assigning colors to grid lines:
- Gray: ;
- Red: Returned;
- Green: Always delivered;
- Yellow: Delivered/Returned;
- White: First entry.

I already have a function done where I check the criticism of the document, but it is a carnival within Datagridview. Follow the example in the image:

I wanted to know if there is a way for me to organize this Datagridview by color, first all gray lines, then red lines, then green lines, then yellow lines and finally all white lines.

Follow my current code:

Imports MySql.Data.MySqlClient

Public Class salvar_arquivo
    Dim sqlcoon = New MySqlConnection '' conexao
    Dim command As MySqlCommand
    Private datagrid As New DataTable
    ' load do form
    Private Sub salvar_arquivo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    '' carrega todo os monitoramento do banco de dados cad_monitoramento
    Dim datagrid As New DataTable
    ' INSERE COLUNAS NO GRIDVIEW
    datagrid.Columns.Add("Identificação")
        datagrid.Columns.Add("Protocolo")
        datagrid.Columns.Add("Destinatario")
        datagrid.Columns.Add("Endereço")
        datagrid.Columns.Add("Bairro")
        datagrid.Columns.Add("Cep")
        datagrid.Columns.Add("Data")
        datagrid.Columns.Add("CNPF/CPF")
        datagrid.Columns.Add("Situacao")
        datagrid.Columns.Add("Cliente")
        datagrid.Columns.Add("data_processamento")
        datagrid.Columns.Add("data_vencimento")
        datagrid.Columns.Add("numeracao")
        DataGridView1.DataSource = datagrid
        ' inserindo o a largura das colunas

        ' deixando invisivel a coluna do banco de dados
        DataGridView1.Columns("Situacao").Visible = False
        DataGridView1.Columns("Cliente").Visible = False
        DataGridView1.Columns("data_processamento").Visible = False
        DataGridView1.Columns("data_vencimento").Visible = False
        DataGridView1.Columns("numeracao").Visible = False
    ' ORDENAS OS TITULOS DA COLUNA NO CENTRALIZADO
    DataGridView1.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(3).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(4).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(5).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(6).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(7).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(8).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(9).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(10).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(11).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns(12).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter


        txtcod.Select()
    End Sub

    ' butao selecionar e ler arquivo
    Private Sub btnarquivo_Click(sender As Object, e As EventArgs) Handles btnarquivo.Click
        selecionar()

End Sub


    ' Função para ler o arquivo linha por linha separado por tab
    Private Sub selecionar()
        abrir.ShowDialog()
        '  Dim x As String = ("62210980259, 10507378001020")
        Dim separador As Char

        Dim dr As DataRow
    ' INSERE COLUNAS NO GRIDVIEW
    Try

            datagrid.Columns.Add("Identificação")
            datagrid.Columns.Add("Protocolo")
            datagrid.Columns.Add("Destinatario")
            datagrid.Columns.Add("Endereço")
            datagrid.Columns.Add("Bairro")
            datagrid.Columns.Add("Cep")
            datagrid.Columns.Add("Data")
            datagrid.Columns.Add("CNPF/CPF")
            datagrid.Columns.Add("Situacao")
            datagrid.Columns.Add("Cliente")
            datagrid.Columns.Add("data_processamento")
            datagrid.Columns.Add("data_vencimento")
            datagrid.Columns.Add("numeracao")

            DataGridView1.AutoResizeColumns()
            DataGridView1.AutoResizeRows()

            dr = datagrid.NewRow()

        '
        ' FAZ A LEITURA DO ARQUIVO - COM EXPAÇAMENTO POR TAB
        Dim arquivo As New System.IO.StreamReader(abrir.FileName)
            separador = vbTab
            ' faz um split de leitura de linha a linha do arquivo
            While arquivo.Peek() <> -1
                datagrid.Rows.Add(arquivo.ReadLine().Split(separador))
            End While
            DataGridView1.DataSource = datagrid

            TextBox2.Text = DataGridView1.RowCount.ToString
        '
        ' chama a função de critica do arquivo colocando cores nas linhas de acordo com a sentaça
        relacionar_cnpj()


        '
    Catch ex As Exception
            MessageBox.Show("ex.Message")

        End Try
    End Sub

' click f2 para abrir o form de pesquisa de selecionar cliente 
Private Sub txtcod_KeyUp(sender As Object, e As KeyEventArgs) Handles txtcod.KeyUp
        If e.KeyCode = Keys.F2 Then

    End If
    End Sub

    Private Sub salvar_arquivo_KeyPress(sender As Object, e As KeyPressEventArgs) Handles Me.KeyPress
        If AscW(e.KeyChar) = 27 Then  ' vbKeyReturn = Tecla Esc
            Me.Close()
        End If
    End Sub


Private Sub salvar_arquivo_Closed(sender As Object, e As EventArgs) Handles Me.Closed
    txtcod.Clear()
    txtrazao.Clear()
End Sub

' funcção de critica do arquivo - cada sentença mostra uma cor diferente no grid.
Private Sub relacionar_cnpj()
    Dim COMMAND2 As MySqlCommand
    Using sqlcoon As MySqlConnection = GetConnectionMySQL()

        Try
            Dim READER2 As MySqlDataReader


            sqlcoon.Open()

            For r As Integer = 0 To DataGridView1.Rows.Count - 1
                Dim Query As String
                Query = "select cnpjcpf_cnpjcpf,cnpjcpf_entregue,cnpjcpf_devolvido,cnpjcpf_cancelado from speed.cnpjcpf where cnpjcpf_cnpjcpf ='" & DataGridView1.Rows(r).Cells(7).Value.ToString() & "'"

                COMMAND2 = New MySqlCommand(Query, sqlcoon)
                READER2 = COMMAND2.ExecuteReader


                While READER2.Read
                    Dim cnpjcpf_cnpjcpf = READER2.GetString("cnpjcpf_cnpjcpf")
                    Dim cnpjcpf_entregue = READER2.GetString("cnpjcpf_entregue")
                    Dim cnpjcpf_devolvido = READER2.GetString("cnpjcpf_devolvido")
                    Dim cnpjcpf_cancelado = READER2.GetString("cnpjcpf_cancelado")

                    ' se for a primeira vez do registro do cnpj
                    If cnpjcpf_entregue = "N" And cnpjcpf_devolvido = "N" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.White

                        ' se for devolvido
                    ElseIf cnpjcpf_entregue = "N" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.IndianRed

                        ' se for cancelado
                    ElseIf cnpjcpf_entregue = "N" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "S" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Silver

                        ' se for sempre entregue
                    ElseIf cnpjcpf_entregue = "S" And cnpjcpf_devolvido = "N" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.ForestGreen
                        ' se for entregue e ja volto como devolvido (Ambos)
                    ElseIf cnpjcpf_entregue = "S" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "N" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Yellow
                        ' se for entregue e ja volto como devolvido e cancelado 
                    ElseIf cnpjcpf_entregue = "S" And cnpjcpf_devolvido = "S" And cnpjcpf_cancelado = "S" Then

                        DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Yellow
                        End If


                End While

                READER2.Close()
            Next

        Catch ex As MySqlException
            MsgBox(ex.Message)
        Finally

            sqlcoon.Close()
        End Try
    End Using
End Sub
End Class

Answer 1

You can create a column Unbound on your grid and call it "Index", for example, and you would assign numbers in the order you want the colors to appear:

• Index = 1 -> Grey lines;
• Index = 2 -> Red lines;
• Index = 3 -> Lines of green color;
• Index = 4 -> Yellow lines;
• Index = 5 -> White lines;

And then I’d sort the grid by that column, sort of like this:

DataGridView1.Rows(r).Cells("IndiceCor").Value = 1   'Linha Cinza.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 2   'Linha Vermelha.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 3   'Linha Verde.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 4   'Linha Amarela.
DataGridView1.Rows(r).Cells("IndiceCor").Value = 5   'Linha Branca.

'[...]

DataGridView1.Sort(DataGridView1.Columns("IndiceCor"), ListSortDirection.Ascending)

The problem is that this way you could only organize by color, and could not put as a second option the CNPJ, for example. So another option would be to put this column virtual with the direct color index in a Datatable, for example, that has the data from your database, so you could use more than one field in the organization.