How to recover index of column in VBA-CodePudding

Sub Bouton7()

    Dim Derniere_ligne As Long
    Dim ligne_en_cours As Long
    Dim libelle As String

    Derniere_ligne = Cells(Rows.Count, 2).End(xlUp).Row
    
For ligne_en_cours = 2 To Derniere_ligne

    libelle = Cells(ligne_en_cours, 5).Value


    If libelle = "XY" Then
     Rows(ligne_en_cours).Interior.ColorIndex = 4     'this will change colour for whole row
End If

    Next

End Sub

Here I color line when libelle = "XY

But I would like to replace libelle = Cells(ligne_en_cours, 5).Value

per libelle = Cells(ligne_en_cours, index (CODE_LIBELLE) ).Value

Because in my table, CODE_LIBELLE is the 5th column, so I would like to recover the index of the name of column.

Thanks

ps :

I did that to recover index column of code libelle :

Derniere_ligne = Cells(Rows.Count, 3).End(xlUp).Row

Set ws = Sheets("Feuil4")

For c = 1 To 6
rec = ws.Cells(1, c).Value
If rec = "CODE_LIBELLE" Then
rec2 = c

End If

Next c

but my c is always the end of my loop and not the value of rec2

CodePudding user response：

Something like this should work:

Sub Bouton7()
    Dim Derniere_ligne As Long
    Dim ligne_en_cours As Long
    Dim libelle As String, m, ws As Worksheet
    
    Set ws = ActiveSheet ' or whatever
    
    'locate the header position in Row1
    m = Application.Match("CODE_LIBELLE", ws.Rows(1), 0)
    If IsError(m) Then    'm will be an error value if no match
        MsgBox "Required header not found"
        Exit Sub
    End If
    
    For ligne_en_cours = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        If ws.Cells(ligne_en_cours, m).Value = "XY" Then
            ws.Rows(ligne_en_cours).Interior.ColorIndex = 4
        End If
    Next
End Sub