Function CountByColor(InputRange As Range, ColorRange As Range) As Long
Dim cl As Range
Dim TempCount As Long
TempCount = 0
For Each cl In InputRange.Cells
If cl.Interior.Color = ColorRange.Cells(1, 1).Interior.Color Then
TempCount = TempCount + 1
End If
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionPublic Function CountByColor(InputRange As Range, ColorRange As Range) As Long2) (et TRES IMPORTANT) : où l'écrire :
Dim cl As Range
For Each cl In InputRange.Cells
If cl.Interior.Color = ColorRange.Interior.Color Then
CountByColor = CountByColor + 1
End If
Next cl
Set cl = Nothing
End Function
lettre = "D"4) il va de soi que la cellule concernée doit être formatée en nombre
mem_debut = 7
compteur3 = 10
ActiveSheet.Cells(11, 4).Formula = "=CountByColor(" & lettre & "$" & CStr(mem_debut) & ":" & lettre & "$" & CStr(compteur3) & ",$O$2)"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lettre = "D"
mem_debut = 7
compteur3 = 10
ActiveSheet.Cells(11, 4).Formula = "=CountByColor(" & lettre & "$" & CStr(mem_debut) & ":" & lettre & "$" & CStr(compteur3) & ",$O$2)"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)L'inconvénient, pour chacune de ces deux solutions, est qu'Excel travaillera chaque fois que ru changeras de sélection et non uniquement lorsque nécessaire.
Me.Calculate
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Je vais au dodo ...
Static titi As Range
If titi Is Nothing Then Set titi = Target
If (Not Application.Intersect(Target, Range("D2:D10")) Is Nothing) Or (Not Application.Intersect(Target, Range("O2")) Is Nothing) Then
Me.Calculate
ElseIf Application.Intersect(Target, Range("D2:D10")) Is Nothing Or (Application.Intersect(Target, Range("O2")) Is Nothing) Then
If (Not Application.Intersect(titi, Range("D2:D10")) Is Nothing) Or (Not Application.Intersect(titi, Range("O2")) Is Nothing) Then
Me.Calculate
End If
End If
Set titi = Target
End Sub
ElseIf Application.Intersect(Target, Range("D2:D10")) Is Nothing And (Application.Intersect(Target, Range("O2")) Is Nothing) Thence n'est pas Or, qu'il fallait, mais And, bien sûr.