I try do color lines who correspond to a conditon, Here my database :

I would like to color line when libelle=XV28

I did it :

Option Compare Text

Sub Bouton3_Cliquer()

    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 = "XV28" Then
        libelle = Cells(ligne_en_cours, 5).Value
        Cells(ligne_en_cours).Interior.ColorIndex = 4

        End If
    Next


End Sub

But it doesn't work , I have only first line in color but it is name's column

Thanks for reading me

PS: I would like to know how I can have number of column without say myself.

For example, in this code :

Sub reset()

    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, 4).Value


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


    Next


End Sub

I say

libelle = Cells(ligne_en_cours, 4).Value

But I would like to replace the 4 per the index of libelle who would return me 4 becuase sometimes, I can have 1 column more and then all would be false.

Thanks in advance

CodePudding user response：

Maybe like this

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

If you want one cell you need to provide row and column number cells(RowNumber, ColumnNumber)
Or you can use range like this
Range("A" & ligne_en_cours & ":D" & ligne_en_cours).Interior.ColorIndex = 4

CodePudding user response：

Few things I can see that would be an issue:

• If libelle = "XV28" Then needs to read XZ
• Cells(rowNum,colNum) has a row & column component (you only have a row in your If statement)... maybe you want Rows() rather than Cells()
• Verify that you're working in Columns(5)/Range("E:E")

If those criteria are all met, you should be rocking and rolling.

CodePudding user response：

I would like to know how I can have number of column without say myself.

For example, in this code :

Sub reset()

    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, 4).Value


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


    Next


End Sub

I say

libelle = Cells(ligne_en_cours, 4).Value

But I would like to replace the 4 per the index of libelle who would return me 4 becuase sometimes, I can have 1 column more and then all would be false.

Thanks in advance