Comment trouver a dernière ligne colorée d'un tableau excel avec VBA?

la couleur est mise automatiquement par la macro.

Voici le code

Dim sousdossiercompany, sousdossierinvoicing As Object
Dim fso As Object
Dim ws As Worksheet



Set objShell = New Shell

For Each objet In objShell.Windows
     
 
'--------------------------------------------------kontrolleraansokan-------------------------------
    If TypeName(objet.Document) = "HTMLDocument" Then
         Set IE = objet
         
                IE.Visible = True


            Set maPageHtml = IE.Document

            Set Htmlcompanyname = maPageHtml.body.all(26)
                
                company_name = Left(Htmlcompanyname.innerText, Len(Htmlcompanyname.innerText) - 1)

                Debug.Print company_name

                        Set supplier_Vat_number = maPageHtml.getElementById("txtMomsRegNr")
                        Set supplier_name = maPageHtml.getElementById("txtLeverantorNamn")
                        Set supplier_address = maPageHtml.getElementById("txtLeverantorAdress")
                        Set supplier_land = maPageHtml.getElementById("cboFakturaLandskod")
                        Set invoice_date = maPageHtml.getElementById("txtFakturadatum")
                        Set Invoice_number = maPageHtml.getElementById("txtFakturanummer")
                        Set taxable_amount = maPageHtml.getElementById("txtBeskattningsunderlag")
                        Set received_vat = maPageHtml.getElementById("txtMomsDebiteratBelopp")
                        Set vat_percent = maPageHtml.getElementById("txtAvdragsprocent")
                        Set applied_vat = maPageHtml.getElementById("txtAnsoktMoms")
                        Set nature_service = maPageHtml.getElementById("cboVarukod")
                        Set detail_service = maPageHtml.getElementById("cboVarukodSub")
                        

                        supplier_Vat_number.Value = ""
                       supplier_name.Value = ""
                       supplier_address.Value = ""
                       'supplier_land.Value = ws.Range("J" & i)
                       invoice_date.Value = ""
                       Invoice_number.Value = ""
                       taxable_amount.Value = ""
                       received_vat.Value = ""
                       vat_percent.Value = ""
                       applied_vat.Value = ""
                       nature_service.Value = ""
                       detail_service.Value = ""


                            Set ws = ActiveWorkbook.Worksheets(2)



'---------------- attribuer les valeurs aux cases du formulaire--------------------------------
                

                
                dercel = ws.Range("a" & Rows.Count).End(xlUp).row + 1
                
                If ws.Range("a1") = "0" Then
               
                      If ws.Range("a2") = "Ryan ref" Then
                      
                      
                      supplier_Vat_number.Value = ws.Range("F" & dercel)
                                supplier_name.Value = ws.Range("G" & dercel)
                                supplier_address.Value = ws.Range("H" & dercel) & "" & ws.Range("I" & dercel) & "" & ws.Range("J" & dercel) & "" & ws.Range("K" & dercel) & ", " & ws.Range("L" & dercel)
                                'supplier_land.Value = ws.Range("J" & i)
                                invoice_date.Value = Format(ws.Range("D" & dercel), "yyyy-mm-dd")
                                Invoice_number.Value = ws.Range("c" & dercel)
                                taxable_amount.Value = ws.Range("o" & dercel)
                                received_vat.Value = ws.Range("p" & dercel)
                                vat_percent.Value = ""
                                applied_vat.Value = ws.Range("S" & dercel)
                                nature_service.Value = ws.Range("T" & dercel)
                                detail_service.Value = ws.Range("U" & dercel)
                                ws.Range("a" & dercel & ":w" & dercel).Interior.ColorIndex = 6
                      End If
                   End If
                      
                     
                        
                        If ws.Range("a" & dercel & ":x" & dercel).Interior.ColorIndex = 6 Then
                        
                        
                                supplier_Vat_number.Value = ws.Range("F" & i).Offset(1, 0)
                                supplier_name.Value = ws.Range("G" & i).Offset(1, 0)
                                supplier_address.Value = ws.Range("H" & i).Offset(1, 0) & "" & ws.Range("I" & i).Offset(1, 0) & "" & ws.Range("J" & i).Offset(1, 0) & "" & ws.Range("K" & i).Offset(1, 0) & ", " & ws.Range("L" & i).Offset(1, 0)
                                'supplier_land.Value = ws.Range("J" & i)
                                invoice_date.Value = Format(ws.Range("D" & i).Offset(1, 0), "yyyy-mm-dd")
                                Invoice_number.Value = ws.Range("c" & i).Offset(1, 0)
                                taxable_amount.Value = ws.Range("o" & i).Offset(1, 0)
                                received_vat.Value = ws.Range("p" & i).Offset(1, 0)
                                vat_percent.Value = ""
                                applied_vat.Value = ws.Range("S" & i).Offset(1, 0)
                                nature_service.Value = ws.Range("T" & i).Offset(1, 0)
                                detail_service.Value = ws.Range("U" & i).Offset(1, 0)
                                ws.Range("a" & i & ":w" & i).Offset(1, 0).Interior.ColorIndex = 6
                         
                        Else
                        
                                supplier_Vat_number.Value = ws.Range("F" & i + 2)
                                supplier_name.Value = ws.Range("G" & i + 2)
                                supplier_address.Value = ws.Range("H" & i) & "" & ws.Range("I" & i + 2) & "" & ws.Range("J" & i + 2) & "" & ws.Range("K" & i + 2) & ", " & ws.Range("L" & i + 2)
                                'supplier_land.Value = ws.Range("J" & i)
                                invoice_date.Value = Format(ws.Range("D" & i + 2), "yyyy-mm-dd")
                                Invoice_number.Value = ws.Range("c" & i + 2)
                                taxable_amount.Value = ws.Range("o" & i + 2)
                                received_vat.Value = ws.Range("p" & i + 2)
                                vat_percent.Value = ""
                                applied_vat.Value = ws.Range("S" & i + 2)
                                nature_service.Value = ws.Range("T" & i + 2)
                                detail_service.Value = ws.Range("U" & i + 2)
                                ws.Range("a" & i + 2 & ":w" & i + 2).Interior.ColorIndex = 6
                        
                           End If
               
                    
 End If
'-------------------------------------------------------------------------------------------------------
Next objet


'Set IE = Nothing
Set maPageHtml = Nothing
'ws.Close
End Sub

la ligne se colorie automatiquement après que les informations dans le tableau aient été utilisés pour remplir le formulaire en ligne.

Ce que j'aimerais, c'est que lorsque la ligne est colorié, le formulaire se remplisse par les informations de la ligne suivante.

c'est ce que je n'arrive pas à faire. je ne vois pas comment faire. c'est pour cela que j'aimerais trouvé la dernière ligne colorée et utiliser cette information pour passer à la ligne suivante et remplir le formulaire en ligne.

Merci pour ta réactivité