VBA - Count the number of colored cells

October 2016


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.

Related :

This document entitled « VBA - Count the number of colored cells » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.