Créer un formatage de plage dynamique, Excel VBA
Voici un code VBA détaillé pour créer un formatage de plage dynamique dans Excel, accompagné d’une explication sur son fonctionnement :
Scénario :
Nous allons créer une plage dynamique qui s’ajuste automatiquement en fonction de l’entrée de données et appliquer un formatage conditionnel pour mettre en évidence certaines cellules dans cette plage dynamique. L’objectif est de formater la plage de manière dynamique au fur et à mesure que de nouvelles données sont ajoutées ou supprimées.
Guide étape par étape :
- Définir une plage dynamique nommée : Tout d’abord, nous définissons une plage dynamique qui va s’étendre ou se contracter en fonction des données d’une colonne spécifique. Imaginons que nous travaillons avec des données dans les colonnes A à C, à partir de la ligne 1.
- Formatage conditionnel : Nous appliquerons également un formatage conditionnel pour mettre en évidence les cellules qui satisfont certains critères. Par exemple, nous pouvons vouloir mettre en surbrillance les cellules de la colonne B dont la valeur est supérieure à 100.
Code VBA :
Sub CreateDynamicRangeAndApplyFormatting()
Dim ws As Worksheet
Dim dynamicRange As Range
Dim lastRow As Long
Dim dataRange As Range
' Définir l'objet feuille de calcul
Set ws = ThisWorkbook.Sheets("Sheet1")
' Étape 1 : Identifier la dernière ligne de données dans la colonne A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Étape 2 : Définir la plage dynamique de A1 à C (basée sur la dernière ligne)
Set dataRange = ws.Range("A1:C" & lastRow)
' Étape 3 : Effacer tous les formats conditionnels existants dans la plage
dataRange.FormatConditions.Delete
' Étape 4 : Appliquer un formatage conditionnel - Mettre en surbrillance les cellules de la colonne B > 100
With dataRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="100")
.Interior.Color = RGB(255, 0, 0) ' Couleur de fond rouge pour les valeurs supérieures à 100
.Font.Color = RGB(255, 255, 255) ' Couleur de police blanche
End With
' Étape 5 : Appliquer une bordure à la plage dynamique
With dataRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = RGB(0, 0, 0)
.TintAndShade = 0
.Weight = xlThin
End With
' Étape optionnelle 6 : Créer une plage dynamique nommée
' Cela va créer une plage dynamique appelée "DynamicRange" dans le classeur
ThisWorkbook.Names.Add Name:="DynamicRange", RefersTo:=dataRange
' Avertir l'utilisateur que la plage dynamique et le formatage ont été appliqués
MsgBox "La plage dynamique et le formatage ont été appliqués avec succès!", vbInformation
End Sub
Explication :
1. Définir l’objet feuille de calcul (ws) :
- Nous définissons la feuille de calcul sur laquelle la plage sera appliquée. Dans cet exemple, nous utilisons « Sheet1 ».
2. Trouver la dernière ligne de données :
- Nous déterminons la dernière ligne contenant des données dans la colonne A en utilisant ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row. Cela garantit que la plage dynamique s’ajuste en fonction de la longueur des données, même si des lignes sont ajoutées ou supprimées.
3. Définir la plage dynamique (dataRange) :
- La plage est définie de A1 à la dernière ligne de la colonne C (ws.Range(« A1:C » & lastRow)). Cela s’étendra ou se contractera en fonction des données présentes.
4. Effacer les formats conditionnels existants :
- Avant d’appliquer de nouveaux formats conditionnels, nous effaçons tous les formats existants dans la plage à l’aide de dataRange.FormatConditions.Delete.
5. Appliquer le formatage conditionnel :
- Nous utilisons la méthode .FormatConditions.Add pour appliquer une règle où toute valeur supérieure à 100 dans la colonne B sera mise en surbrillance avec un fond rouge et une police blanche. Cela permet de mettre en évidence les cellules dont la valeur dépasse un certain seuil.
6. Appliquer une bordure à la plage :
- Une bordure inférieure est appliquée à la plage dynamique à l’aide de dataRange.Borders(xlEdgeBottom). La bordure apparaît sous la plage, donnant ainsi une apparence plus structurée.
7. Créer une plage dynamique nommée (optionnel) :
- La ligne ThisWorkbook.Names.Add Name:= »DynamicRange », RefersTo:=dataRange crée une plage nommée appelée DynamicRange qui fait référence à la plage dynamique. Cette plage fera toujours référence aux données actuelles des colonnes A à C.
8. Message de confirmation :
- Une fois que la plage et le formatage ont été appliqués, un message de confirmation est affiché à l’utilisateur.
Conclusion :
Ce code VBA montre comment créer une plage dynamique qui s’ajuste automatiquement au fur et à mesure que les données changent dans votre feuille de calcul. Il applique également un formatage conditionnel pour mettre en évidence les valeurs qui répondent à des critères spécifiques et ajoute des bordures pour améliorer la lisibilité. Vous pouvez modifier les règles de formatage et la plage en fonction de vos besoins spécifiques.