Formater les lignes d'un tableau en fonction d'une cellule

Le 13/11/2018 à 04:10

Bonjour a tous,

Tout d'abord, merci de votre précieuse aide et conseille.

Voila, je veux formater la valeur des ligne de mon tableau en fonction de la valeur d'une cellule. Pour cela, j'utilise un bouton qui va executer

mon code et formater les cellules. Voir mon code ci-dessous.

Const str_NOM_DE_LAFEUILLE As String = "Échéancier"
Const str_LAPLAGE_DE_REFERENCE As String = "C11"
Const str_NOM_ENTETEPLAGE_AFORMATER As String = "DATE D’ÉCHÉANCE"
Const str_NOM_TABLEAU_AFORMATER As String = "ÉchéancierPaiement"
Const int_LigneDeRefe_Formater As Integer = 11
Const int_ColonneDeRef_Formater As Integer = 3
Dim rng_PlageAReferencer As Range

Sub formaterTabSelonDate(ByRef str_NOMFEUILL_AFORMATER As String, _
                         ByRef rng_PalgeAFormater As Range, _
                         ByRef str_NOM_ENTETEPLAGE_AFORMATER As String, _
                         ByRef int_LigneDeRefe_Formater As Integer, _
                         ByRef int_ColonneDeRef_Formater As Integer, _
                         ByRef str_NOM_TABLEAU_AFORMATER As String)

    'Variable objet
    Dim Cellule As Range
    Dim NbrDeColonne As Integer

    'Variable
    Dim d_CETTE_ANNEE As Variant
    Dim d_CE_MOIS As Variant

    '***** Initialiser les variables *****'
      NbrDeColonne = Cells(int_LigneDeRefe_Formater, int_ColonneDeRef_Formater).End(xlToRight).Column
      d_CETTE_ANNEE = Year(Now)
      d_CE_MOIS = Month(Now)

    If Cells(int_LigneDeRefe_Formater, rng_PalgeAFormater.Column).Value = str_NOM_ENTETEPLAGE_AFORMATER Then

        For Each Cellule In Sheets(str_NOMFEUILL_AFORMATER).Range(str_NOM_TABLEAU_AFORMATER)

            If (Year(rng_PalgeAFormater.Value) = d_CETTE_ANNEE) Then

                If Month(rng_PalgeAFormater.Value) < d_CE_MOIS Then

                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = 192 ' rouge
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbWhite 'police_MOIS_PRECEDANT
                          Exit For
                ElseIf (Month(rng_PalgeAFormater.Value) = d_CE_MOIS) Then

                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = 65535 'jaune
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbBlack 'police_CETTEANNEE_CEMOIS
                        Exit For
                ElseIf (Year(rng_PalgeAFormater.Value) < d_CETTE_ANNEE) Then

                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = vbBlack ' vbBlack
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbWhite
                          Exit For
                End If
         Next Cellule
    Else
        Exit Sub

    End If
End Sub

Or, au débogage l'erreur de compilation suivante a été générer: Next sans For.

Pourriez-vous me dire ce qui ne fonctionne pas avec mon code. Merci

Le 13/11/2018 à 04:30

Bonjour jlkKaty07,

essaye ceci :

Option Explicit

Const str_NOM_DE_LAFEUILLE As String = "Échéancier"
Const str_LAPLAGE_DE_REFERENCE As String = "C11"
Const str_NOM_ENTETEPLAGE_AFORMATER As String = "DATE D’ÉCHÉANCE"
Const str_NOM_TABLEAU_AFORMATER As String = "ÉchéancierPaiement"
Const int_LigneDeRefe_Formater As Integer = 11
Const int_ColonneDeRef_Formater As Integer = 3

Dim rng_PlageAReferencer As Range

Sub formaterTabSelonDate(ByRef str_NOMFEUILL_AFORMATER As String, _
                         ByRef rng_PalgeAFormater As Range, _
                         ByRef str_NOM_ENTETEPLAGE_AFORMATER As String, _
                         ByRef int_LigneDeRefe_Formater As Integer, _
                         ByRef int_ColonneDeRef_Formater As Integer, _
                         ByRef str_NOM_TABLEAU_AFORMATER As String)

  'Variable objet
  Dim Cellule As Range
  Dim NbrDeColonne As Integer

  'Variable
  Dim d_CETTE_ANNEE As Variant
  Dim d_CE_MOIS As Variant

  '***** Initialiser les variables *****'
  NbrDeColonne = Cells(int_LigneDeRefe_Formater, int_ColonneDeRef_Formater).End(xlToRight).Column
  d_CETTE_ANNEE = Year(Now)
  d_CE_MOIS = Month(Now)

  If Cells(int_LigneDeRefe_Formater, rng_PalgeAFormater.Column).Value <> str_NOM_ENTETEPLAGE_AFORMATER Then Exit Sub

  For Each Cellule In Sheets(str_NOMFEUILL_AFORMATER).Range(str_NOM_TABLEAU_AFORMATER)
    If (Year(rng_PalgeAFormater.Value) = d_CETTE_ANNEE) Then
      If Month(rng_PalgeAFormater.Value) < d_CE_MOIS Then
        Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = 192 ' rouge
        Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbWhite 'police_MOIS_PRECEDANT
        Exit For
      ElseIf (Month(rng_PalgeAFormater.Value) = d_CE_MOIS) Then
        Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = 65535 'jaune
        Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbBlack 'police_CETTEANNEE_CEMOIS
        Exit For
      ElseIf (Year(rng_PalgeAFormater.Value) < d_CETTE_ANNEE) Then
        Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = vbBlack ' vbBlack
        Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbWhite
        Exit For
      End If
    End If
  Next Cellule

End Sub

dhany

Le 13/11/2018 à 05:33

Merci dhany,

J'ai modifier le code ainsi selon ton conseil

Const str_NOM_DE_LAFEUILLE As String = "Échéancier"
Const str_LAPLAGE_DE_REFERENCE As String = "C12"
Const str_NOM_ENTETEPLAGE_AFORMATER As String = "DATE D’ÉCHÉANCE"
Const str_NOM_TABLEAU_AFORMATER As String = "ÉchéancierPaiement"
Const int_LigneDeRefe_Formater As Integer = 11
Const int_ColonneDeRef_Formater As Integer = 2
Dim rng_PlageAReferencer As Range

Private Sub cmd_Rafraichir_Click()

    '    'Initialiser la plage
        Set rng_PlageAReferencer = ThisWorkbook.Sheets(str_NOM_DE_LAFEUILLE).Range(str_LAPLAGE_DE_REFERENCE) 

     Call mod_0_Formatage.formaterTabSelonDate(str_NOM_DE_LAFEUILLE, _
                                              rng_PlageAReferencer, _
                                              str_NOM_ENTETEPLAGE_AFORMATER, _
                                              int_LigneDeRefe_Formater, _
                                              int_ColonneDeRef_Formater, _
                                              str_NOM_TABLEAU_AFORMATER)

End Sub

'/********

Sub formaterTabSelonDate(ByRef str_NOMFEUILL_AFORMATER As String, _
                         ByRef rng_PalgeAFormater As Range, _
                         ByRef str_NOM_ENTETEPLAGE_AFORMATER As String, _
                         ByRef int_LigneDeRefe_Formater As Integer, _
                         ByRef int_ColonneDeRef_Formater As Integer, _
                         ByRef str_NOM_TABLEAU_AFORMATER As String)    

    'Variable objet
    Dim Cellule As Range
    Dim NbrDeColonne As Integer

    'Variable
    Dim d_CETTE_ANNEE As Variant
    Dim d_CE_MOIS As Variant

    '***** Initialiser les variables *****'
      NbrDeColonne = Cells(int_LigneDeRefe_Formater, int_ColonneDeRef_Formater).End(xlToRight).Column
      d_CETTE_ANNEE = Year(Now)
      d_CE_MOIS = Month(Now)

    If Cells(int_LigneDeRefe_Formater, rng_PalgeAFormater.Column).Value <> str_NOM_ENTETEPLAGE_AFORMATER Then Exit Sub

        For Each Cellule In Sheets(str_NOMFEUILL_AFORMATER).Range(str_NOM_TABLEAU_AFORMATER)

            If (Year(rng_PalgeAFormater.Value) = d_CETTE_ANNEE) Then

                If Month(rng_PalgeAFormater.Value) < d_CE_MOIS Then
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = 192 ' rouge
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbWhite 'police_MOIS_PRECEDANT
                          Exit For

                ElseIf (Month(rng_PalgeAFormater.Value) = d_CE_MOIS) Then
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = 65535 'jaune
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbBlack 'police_CETTEANNEE_CEMOIS
                        Exit For

                ElseIf (Year(rng_PalgeAFormater.Value) < d_CETTE_ANNEE) Then
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Interior.Color = vbBlack ' vbBlack
                    Range(Cells(rng_PalgeAFormater.Row, int_ColonneDeRef_Formater), _
                          Cells(rng_PalgeAFormater.Row, NbrDeColonne)).Font.Color = vbWhite
                          Exit For
                End If
            End If
         Next Cellule
End Sub

j'ai toujours un problème. Mon but est de parcourir la table en entier et faire la mise en forme de chaque ligne du tableau

dépendamment de la date d'échéance de chaque cellule de la colonne '"Date échéance". En pièce jointe un screeshot de mon tableau.

Je veux que tout les lignes du tableau soit traité.

Merci de votre aide

2018 11 12 23 02 33 window
Rechercher des sujets similaires à "formater lignes tableau fonction"