Option Explicit
Function CompteTextCouleur(PlageText As Range, ref As String, PlageCouleur As Range, myColor As Range) As Integer
Application.Volatile 'ceci pour que la formule se recalcule après chaque update de ta feuille
Dim i as integer
Dim d As Range 'un object cellule
For i = 1 To PlageText.Count Step 2 'boucle sur ta ligne d'entête avec un step 2 car tes cellules sont fusionnées par deux
'de ce fait i aura comme valeur 1 puis 3, puis 5...
If CStr(PlageText(i).Value) = ref Then 'si la valeur de la cellule est égale à la valeur de ton paramètre alors vrai et donc il rentre dans la condition
'cstr() permet de convertir la valeur en texte car dans ton besoin tu utilises une valeur texte. cint() pour un integer (un nombre entier de petite taille)
For Each d In PlageCouleur 'boucle sur toutes les cellules de ta plage avec les couleurs
If d.Column = PlageText(i).Column + 1 Then 'si la cellule actuelle (de la boucle) est dans la colonne qui suit (+1) la colonne du texte trouvé alors vrai (donc je rentre dans la condition)
If d.Interior.ColorIndex = myColor.Interior.ColorIndex Then 'test si la cellule est de la même couleur (de base attention, ne fonctionnera pas avec un thème) que la cellule couleur passé en paramètre alors vrai
CompteTextCouleur = CompteTextCouleur + 1 'j'incrémente le résultat de 1 (en gros je prends le résultat précédent que je fais plus 1)
End If 'fin du test sur la couleur
End If'fin du test sur la colonne
Next 'arrivé ici je recommence le for each jusqu'à être passé sur toutes les cellules de ma plage colorie
End If 'fin du test sur le texte recherché
Next 'je retourne au for i tant que i est inférieur au count (total du nombre de colonne)
set d = nothing ' je supprime mon object cellule pour libérer de la mémoire
CompteTextCouleur = CompteTextCouleur 'je retourne le resultat à celui qui à fait appel à la fonction (dans ton cas la cellule de ta feuille)
End Function