VBA - Count the number of colored cells


I'm looking for VBA code under which we can determine the index of the last cell (predefined color) in a column columns.

Example: The column containing colored cells up to the line 650, how to get this 650?


Here is a small code, for this purpose:

'for the test we will use a yellow background = 6 sur 20 ligne
' the last line is no 30

     a = Range("A1").Interior.ColorIndex
     col = CountColor_bacgroung(Range("A1:A120"), 6)
     s = MsgBox("the last cell is : " & col, vbInformation, " line of color")

End Sub

Function CountColor_bacgroung(Index As Range, Color As Long) As Long
Dim C As Variant

Dim X
X = 0
For Each C In Plage
    If C.Interior.ColorIndex = Color Then
    X = C.Row  
    End If
CountColor_bacgroung= X
End Function

Thanks to Bidouilleu_R for this tip.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « VBA - Count the number of colored cells », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).