Function Nb_Si_Color(rng As Range, Couleur As Long, Optional GetTable As Boolean = False) As Variant
Dim Cell As Range
Dim Count As Long
Dim values() As Variant
Dim i As Long
Count = 0
i = 0
For Each Cell In rng
If Cell.Interior.Color = Couleur Then
Count = Count + 1
If GetTable Then
ReDim Preserve values(i)
values(i) = Cell.Value
i = i + 1
End If
End If
Next Cell
If GetTable Then
Nb_Si_Color = values
Else
Nb_Si_Color = Count
End If
End Function
Sub test1()
Dim result As Long
result = Nb_Si_Color(Range("A1:K10"), RGB(255, 0, 0))
Debug.Print result
End Sub
Sub test2()
Dim values As Variant
Dim i As Long
Dim sumValues As Double
Dim selectedColor As Long
Dim red As Long, green As Long, blue As Long
Dim ws As Worksheet
Dim dataRange As Range
Dim hasValues As Boolean
Set ws = ActiveSheet
Set dataRange = ws.UsedRange
selectedColor = ActiveCell.Interior.Color
red = selectedColor Mod 256
green = (selectedColor \ 256) Mod 256
blue = (selectedColor \ 65536) Mod 256
values = Nb_Si_Color(dataRange, selectedColor, True)
sumValues = 0
hasValues = False
If IsArray(values) Then
If UBound(values) >= LBound(values) Then
For i = LBound(values) To UBound(values)
If Not IsEmpty(values(i)) And values(i) <> "" Then
hasValues = True
Exit For
End If
Next i
If Not hasValues Then Exit Sub
Debug.Print "Couleur de la cellule sélectionnée (RGB) : " & red & ", " & green & ", " & blue
Debug.Print "Valeurs des cellules de la couleur sélectionnée :"
For i = LBound(values) To UBound(values)
If Not IsEmpty(values(i)) And values(i) <> "" Then
Debug.Print values(i)
If IsNumeric(values(i)) Then
sumValues = sumValues + CDbl(values(i))
End If
End If
Next i
If sumValues <> 0 Then Debug.Print "Somme des valeurs numériques de la couleur sélectionnée : " & sumValues
End If
End If
End Sub
Sub test3()
Dim ws As Worksheet
Dim dataRange As Range
Dim Cell As Range
Dim colorDict As Object
Dim colorKey As Variant
Dim red As Long, green As Long, blue As Long
Dim values As Variant
Dim i As Long
Dim sumValues As Double
Set colorDict = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
Set dataRange = ws.UsedRange
For Each Cell In dataRange
If Cell.Interior.Color <> RGB(255, 255, 255) Then
colorKey = Cell.Interior.Color
If Not colorDict.Exists(colorKey) Then
colorDict.Add colorKey, 0
End If
If IsNumeric(Cell.Value) Then
colorDict(colorKey) = colorDict(colorKey) + CDbl(Cell.Value)
End If
End If
Next Cell
For Each colorKey In colorDict.Keys
red = colorKey Mod 256
green = (colorKey \ 256) Mod 256
blue = (colorKey \ 65536) Mod 256
Debug.Print "Couleur (RGB) : " & red & ", " & green & ", " & blue & " | Somme des valeurs : " & colorDict(colorKey)
Next colorKey
End Sub
