0
Thanks

A few words of thanks would be greatly appreciated.

VBA - Count the number of colored cells




Issue


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?

Solution


Here is a small code, for this purpose:


Subcolor_count()
'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
Next
CountColor_bacgroung= X
End Function


Thanks to Bidouilleu_R for this tip.
0
Thanks

A few words of thanks would be greatly appreciated.

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.
Related
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).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!