MENU
  1. Accueil
  2. MS Office
  3. Excel
  4. Excel & VBA : compter les cellules colorées (jaune) pour ajuster automatiquement une durée et simplifier vos formules

Excel & VBA : compter les cellules colorées (jaune) pour ajuster automatiquement une durée et simplifier vos formules

Excel

Vous colorez des dates en jaune dans Excel et vous ajoutez encore “+2” au bout de vos formules ? Remplacez cet ajustement arbitraire par un calcul fiable qui compte automatiquement les cellules jaunes et majore la durée en conséquence, tout en simplifiant vos formules.

Sommaire

Compter les cellules colorées pour ajuster automatiquement un calcul

Problème traité

Dans un plan de cours (ou tout tableau planifié) sous Excel, certaines dates sont surlignées en jaune. Chaque date jaune doit ajouter 1 heure à la durée totale du cours. Jusqu’ici, la formule ressemblait à :

=IF($N$10="","",IF(O13="","",$O13*$N$10))+2

Ce +2 était fixe : il ne reflète pas le nombre réel de cellules jaunes. L’objectif est de le remplacer par un comptage automatique des cellules jaunes dans la plage (par ex. D13:N13) et d’ajouter autant d’heures que de cellules détectées.

Solution rapide (UDF VBA) — pas à pas

Nous allons créer une fonction personnalisée (UDF) nommée CountYellow qui renvoie le nombre de cellules au fond jaune dans une plage. Elle pourra ensuite être appelée directement dans une cellule, au même titre que SUM ou COUNTIF.

  1. Ouvrir l’éditeur VBA : Alt + F11 → menu Insertion > Module.
  2. Coller le code suivant dans le nouveau module :
' Version simple : compte les cellules dont le fond est exactement vbYellow
Public Function CountYellow(rng As Range) As Long
    Dim cel As Range
    Application.Volatile           ' La fonction est recalculée avec F9 / Recalcul
    For Each cel In rng.Cells
        If cel.Interior.Color = vbYellow Then
            CountYellow = CountYellow + 1
        End If
    Next cel
End Function

Pourquoi ça marche ? Interior.Color renvoie la couleur de remplissage de la cellule. Si elle est exactement égale à vbYellow, on incrémente le compteur. Application.Volatile force un recalcul de la fonction lors d’un recalcul général.

Formule Excel à utiliser

Dans la cellule Q13 (par exemple), remplacez votre ancienne formule par :

=IF(OR($N$10="",O13=""),"", O13*$N$10 + CountYellow(D13:N13))

Equivalent avec paramètres régionaux français (séparateur « ; » et fonctions localisées) :

=SI(OU($N$10="";O13="");""; O13*$N$10 + CountYellow(D13:N13))
  • O13*$N$10 calcule la durée « de base » (par exemple : nombre de séances × durée d’une séance).
  • CountYellow(D13:N13) renvoie le nombre de cellules à fond jaune dans la ligne des dates.
  • Le +2 fixe disparaît : l’ajustement s’adapte vraiment aux mises en évidence.

Variantes utiles selon vos mises en forme

1) La couleur vient d’une mise en forme conditionnelle (MFC)

Si la cellule est coloriée via une Mise en forme conditionnelle (et pas manuellement), Interior.Color ne reflète pas toujours la couleur visible. Utilisez alors DisplayFormat.Interior.Color qui renvoie la couleur affichée après application des règles :

' Version MFC-friendly : compte la couleur effectivement affichée
Public Function CountYellowCF(rng As Range) As Long
    Dim cel As Range
    Application.Volatile
    For Each cel In rng.Cells
        If cel.DisplayFormat.Interior.Color = vbYellow Then
            CountYellowCF = CountYellowCF + 1
        End If
    Next cel
End Function

Formule feuille :

=SI(OU($N$10="";O13="");""; O13*$N$10 + CountYellowCF(D13:N13))

2) L’« orange-jaune » n’est pas exactement vbYellow

Les thèmes Excel utilisent des teintes (tint) proches du jaune, mais pas identiques à vbYellow. Pour éviter tout écart, comparez la couleur de chaque cellule à celle d’une cellule de référence (ex. $B$2) dont vous fixez le remplissage.

' Compter selon une couleur d'exemple (manuel ou MFC)
Public Function CountByColor(rng As Range, sampleCell As Range, _
                             Optional useDisplayFormat As Boolean = True) As Long
    Dim cel As Range, targetColor As Long, celColor As Long
    Application.Volatile

    If useDisplayFormat Then
        targetColor = sampleCell.DisplayFormat.Interior.Color
    Else
        targetColor = sampleCell.Interior.Color
    End If

    For Each cel In rng.Cells
        If useDisplayFormat Then
            celColor = cel.DisplayFormat.Interior.Color
        Else
            celColor = cel.Interior.Color
        End If
        If celColor = targetColor Then
            CountByColor = CountByColor + 1
        End If
    Next cel
End Function

Formule feuille :

=SI(OU($N$10="";O13="");""; O13*$N$10 + CountByColor(D13:N13; $B$2))

Vous gagnez ainsi en robustesse : modifiez la teinte dans $B$2 et le calcul s’ajuste automatiquement.

3) Paramétrer l’« heures à ajouter par cellule jaune »

Si l’ajout n’est pas +1 heure mais une autre valeur (ex. 1,5 h), passez un paramètre supplémentaire :

Public Function CountByColorAdd(rng As Range, sampleCell As Range, _
                                Optional addPerCell As Double = 1) As Double
    Dim cel As Range, targetColor As Long
    Application.Volatile
    targetColor = sampleCell.DisplayFormat.Interior.Color
    For Each cel In rng.Cells
        If cel.DisplayFormat.Interior.Color = targetColor Then
            CountByColorAdd = CountByColorAdd + addPerCell
        End If
    Next cel
End Function

Formule :

=SI(OU($N$10="";O13="");""; O13*$N$10 + CountByColorAdd(D13:N13; $B$2; 1))

Exemple concret

CelluleValeurRemplissage
D1301/10Jaune
E1308/10—
F1315/10Jaune
G13:N13Autres dates—
O1310 (séances)—
N102 (h/séance)—

Calcul : O13*$N$10 = 10 × 2 = 20 heures de base. Deux cellules jaunes → +2 heures. Résultat : 22.

Gestion du recalcul (couleurs & UDF)

Important : Excel ne déclenche pas toujours le recalcul d’une UDF basée sur la couleur au moment où vous recolorez une cellule. Quelques options :

  • Recalcul manuel : appuyer sur F9 ou Formules > Calculer maintenant.
  • Lier la couleur à une valeur via une Mise en forme conditionnelle (MFC) basée sur un test. Quand la valeur change, la feuille recalcule.
  • Événements VBA : forcer un recalcul lorsque les données changent.

Forcer un recalcul si les valeurs évoluent

Si la couleur jaune est pilotée par des règles de MFC dépendant des dates, utilisez l’événement Worksheet_Calculate pour recalculer la zone :

Private Sub Worksheet_Calculate()
    ' Si vos MFC réagissent à des formules, cette macro forcera le recalcul des UDF
    ' Adaptez la plage en fonction de votre feuille
    Me.Range("Q13:Q200").Calculate
End Sub

Placez ce code dans le module de la feuille (clic droit sur l’onglet de la feuille → Afficher le code).

Performance et bonnes pratiques

  • Les UDF parcourent cellule par cellule. Restreignez la plage au strict nécessaire (D13:N13, pas la ligne entière).
  • Privilégiez une seule UDF par cellule de résultat. Évitez d’appeler la fonction des centaines de fois inutilement.
  • Si votre classeur est lourd, retirez Application.Volatile et déclenchez le recalcul depuis un bouton/macro ou via Worksheet_Calculate.
  • Enregistrez en .xlsm et informez les utilisateurs qu’il faut Activer le contenu (macros).

Fonction personnalisée (UDF) vs Macro : quelles différences ?

On confond souvent « macro » et « fonction VBA ». Voici les différences clés :

AspectMacro (Sub)Fonction personnalisée (UDF)
AppelDepuis Développeur > Macros, un bouton, un raccourciDirectement en cellule : =MaFonction(...)
Renvoie une valeurNon (exécute des actions)Oui (obligatoire)
ArgumentsOptionnels (peu courant côté utilisateur)Souvent requis (rng, cellule d’exemple, etc.)
Usage typiqueAutomatiser des tâches (mise en forme, imports, exports)Étendre les fonctions Excel (comptage couleur, calculs métiers)
VisibilitéListe des macrosIntelliSense dans la barre de formule (après enregistrement en .xlsm)
Dépendance au recalculNon : s’exécute à la demandeOui : sujette aux règles de recalcul d’Excel

Dans notre scénario, CountYellow est une UDF : elle n’apparaît pas dans la boîte Macros, mais s’utilise dans une cellule.

Réduire les formules Excel très longues (LET, noms, LAMBDA, tableaux dynamiques)

Les classeurs pédagogiques ou de suivi de projet s’encombrent vite de formules imbriquées. Voici une méthode concrète pour les rendre lisibles, robustes et rapides.

Nommer des éléments et utiliser LET()

Avec Microsoft 365, LET() permet de nommer des sous-calculs. Exemple :

=LET(
   DureeSeance; $N$10;
   NbSeances; O13;
   Jaunes; CountByColor(D13:N13; $B$2);
   NbSeances*DureeSeance + Jaunes
)

Avantages : une seule évaluation de chaque bloc, une lecture immédiate du sens métier.

Noms définis & plages dynamiques

  • Créez des noms comme DatesCours, DureeSeance, NbSeances. Vous pouvez les rendre dynamiques avec OFFSET ou mieux TAKE/DROP/INDEX (M365).
  • Vos formules deviennent auto-documentées.

Encapsuler la logique récurrente avec LAMBDA()

Quand une formule revient partout, encapsulez-la dans une fonction feuille sans VBA :

=LAMBDA(RngDates; CelluleCouleur; DureeSeance; NbSeances;
   LET(
     Jaunes; CountByColor(RngDates; CelluleCouleur);
     NbSeances*DureeSeance + Jaunes
   )
)

Enregistrez ce LAMBDA sous le nom CalculDureeAjustee, puis utilisez :

=CalculDureeAjustee(D13:N13; $B$2; $N$10; O13)

Tableaux dynamiques pour éviter les copies en cascade

Au lieu de recopier des formules par dizaine de lignes, générez un résultat en tableau et référez-vous à lui :

=LET(
  T; HSTACK(A13:A100; D13:N100; O13:O100);
  FILTER(T; (A13:A100<>"") )
)

Puis appliquez vos agrégations sur ce tableau (ex. MAP, BYROW) pour propager la logique sur chaque ligne, une seule fois.

UDF spécifique si la logique reste trop complexe

Si l’évaluation de notes ou de modules devient un « monstre », créez une UDF dédiée, par ex. ModuleAverage(StudentRow, ModuleIndex) qui encapsule la règle et renvoie directement la moyenne. Vous exposez du sens métier, pas de la plomberie Excel.

Alternatives sans VBA (quand c’est possible)

Compter la couleur est un besoin de présentation. Si la couleur jaune correspond à une règle logique (ex. « date le samedi » ou « ferié »), préférez compter la condition plutôt que la couleur.

Exemple : dates le samedi

Comptez les samedis sur D13:N13 et ajoutez 1 h par samedi :

=SI(OU($N$10="";O13="");""; O13*$N$10 + SOMMEPROD(--(JOURSEM(D13:N13;2)=6)) )
  • JOURSEM(...;2) renvoie 6 pour le samedi (système Lundi=1 … Dimanche=7).
  • Vous éliminez la dépendance à la couleur et gagnez un recalcul exact, instantané.

Exemple : jours fériés stockés dans une liste

Si vous avez une liste nommée Feries, comptez combien de dates tombent sur un férié :

=SI(OU($N$10="";O13="");""; O13*$N$10 + SOMMEPROD(ESTNUM(EQUIV(D13:N13; Feries; 0))) )

Astuce UX : conservez la MFC jaune pour mettre en évidence visuellement les samedis/fériés, mais basez vos calculs sur la règle — pas sur la couleur.

Déploiement, sécurité et compatibilité

Check‑list de mise en service

  • Enregistrez le classeur en .xlsm.
  • Activez l’onglet Développeur (Options Excel → Personnaliser le ruban).
  • Placez les UDF dans un module standard (Insertion > Module).
  • Informez les utilisateurs que les macros doivent être autorisées à l’ouverture.
  • Testez avec des cellules colorées manuellement et via MFC.

Compatibilité Windows / Mac

  • Les UDF Color/DisplayFormat fonctionnent sous Excel Windows et Mac (Microsoft 365/2021+). Sur d’anciennes versions, DisplayFormat peut être absent : fallback sur Interior.Color ou utilisez une règle logique sans couleur.
  • Les séparateurs de liste diffèrent : , (US/EN) vs ; (FR). Adaptez vos formules selon la locale.

Performance

  • Évitez de boucler sur des plages immenses. Préférez des zones ciblées (par lignes de cours).
  • Centralisez le calcul dans une cellule « résumé » par ligne plutôt que dans chaque cellule de la plage.
  • Désactivez Application.Volatile si vous contrôlez le recalcul (bouton, événement).

Troubleshooting : questions fréquentes

« J’ai recoloré une cellule et le résultat n’a pas bougé »

C’est attendu avec les UDF basées sur la couleur. Appuyez sur F9, ou forcez un recalcul ciblé via Worksheet_Calculate, ou — mieux — utilisez une MFC liée à une valeur qui, elle, déclenche le recalcul.

« Le jaune de ma charte n’est pas reconnu »

Il s’agit probablement d’une teinte thème (pas vbYellow). Utilisez CountByColor(...; $B$2) en choisissant une cellule échantillon au bon remplissage.

« La cellule est colorée par MFC et la fonction renvoie 0 »

Remplacez Interior par DisplayFormat (ex. CountYellowCF) pour lire la couleur après application de la MFC.

« Je veux ajouter 1,5 h par date jaune »

Utilisez CountByColorAdd(rng; sample; 1,5) puis additionnez ce résultat à votre durée de base.

« Peut-on éviter le VBA ? »

Oui, si la couleur représente une règle vérifiable (samedi, férié, statut, etc.). Remplacez la détection de couleur par un test logique (JOURSEM, EQUIV, COUNTIF/SOMMEPROD). Vous gagnez en portabilité (.xlsx) et en recalcul instantané.

Recette complète réutilisable

  1. Décidez : compter la couleur (VBA) ou compter la règle (formule pure).
  2. Si couleur :
    • Créez CountByColor et ajoutez une cellule échantillon $B$2 (remplissage jaune de référence).
    • Si MFC : utilisez DisplayFormat et, si besoin, Worksheet_Calculate.
  3. Si règle :
    • Écrivez la condition (JOURSEM, EQUIV sur la liste des fériés, etc.).
    • Comptez avec SOMMEPROD/NB.SI/NB.SI.ENS.
  4. Simplifiez vos formules avec LET, des noms définis et, si besoin, LAMBDA.
  5. Documentez : commentez le module VBA, nommez les zones, ajoutez une note sur le recalcul.

Bloc de code final (copier‑coller)

' ===== Module standard =====
' Compter les cellules affichées avec la même couleur que la cellule d'exemple
Public Function CountByColor(rng As Range, sampleCell As Range, _
                             Optional useDisplayFormat As Boolean = True) As Long
    Dim cel As Range, targetColor As Long, celColor As Long
    Application.Volatile
    If useDisplayFormat Then
        targetColor = sampleCell.DisplayFormat.Interior.Color
    Else
        targetColor = sampleCell.Interior.Color
    End If
    For Each cel In rng.Cells
        If useDisplayFormat Then
            celColor = cel.DisplayFormat.Interior.Color
        Else
            celColor = cel.Interior.Color
        End If
        If celColor = targetColor Then CountByColor = CountByColor + 1
    Next cel
End Function

' Variante : additionne x heures par cellule colorée (x par défaut = 1)
Public Function CountByColorAdd(rng As Range, sampleCell As Range, _
Optional addPerCell As Double = 1) As Double
Dim cel As Range, targetColor As Long
Application.Volatile
targetColor = sampleCell.DisplayFormat.Interior.Color
For Each cel In rng.Cells
If cel.DisplayFormat.Interior.Color = targetColor Then
CountByColorAdd = CountByColorAdd + addPerCell
End If
Next cel
End Function

Formule type :

=SI(OU($N$10="";O13="");""; O13*$N$10 + CountByColor(D13:N13; $B$2))

Formule alternative sans VBA (samedi) :

=SI(OU($N$10="";O13="");""; O13*$N$10 + SOMMEPROD(--(JOURSEM(D13:N13;2)=6)) )

Résumé & bonnes pratiques

  • Précision : compter la couleur « visible » via DisplayFormat si MFC.
  • Souplesse : paramétrer la couleur par une cellule échantillon.
  • Recalcul : F9 ou événements si vous restez sur la logique « couleur ».
  • Simplicité : LET, noms définis, LAMBDA pour rendre vos formules lisibles.
  • Robustesse : si la couleur représente une règle, comptez la règle, pas la couleur.

FAQ technique rapide

QuestionRéponse synthétique
La fonction apparaît-elle dans « Macros » ?Non, c’est une fonction ; tapez =CountByColor(...) dans une cellule.
La couleur de thème est-elle gérée ?Oui, via la cellule échantillon et/ou DisplayFormat.
Quid de la vitesse ?Limitez les plages, évitez les appels multiples, retirez Volatile si nécessaire.
Peut-on gérer plusieurs couleurs ?Créez plusieurs cellules échantillon (jaune, orange…) et additionnez les résultats.
Et si je veux soustraire des heures ?Utilisez CountByColorAdd(...; -1) ou multipliez le résultat par -1.

Pour aller plus loin : quelques idées d’automatisation

  • Bouton « Recalculer les durées » assigné à ActiveSheet.Calculate pour les utilisateurs qui ne veulent pas de F9.
  • Validation de données pour restreindre la saisie des dates et éviter les erreurs (ex. pas de dimanche si votre règle l’exclut).
  • Commentaires de cellule qui expliquent pourquoi la cellule est colorée (trace métier).
  • Feuille de paramètres (durée par séance, couleur de référence, liste des fériés).
Excel
Excel Formules VBA
Sommaire