Count cells that contain fill colour

[bondi26]

Was wandering if someone can help me out with a formula that counts the number of cells that contain fill colour.

Any help will be appreciated.

[jasoncw]

If it is actually fill color (and not conditional formatting), then this procedure will work:

Sub CountCells()
    Dim cell As Range
    Dim i As Long
    
    For Each cell In ActiveSheet.UsedRange
        If cell.Interior.ColorIndex > 0 Then _
            i = i + 1
    Next cell
    
    MsgBox i
End Sub

HTH

Jason

[bondi26]

Hi jasoncw,

am I right to say that your solution will need to be setup in VB or directly into the spreadsheet cell which is what Im after, also where do I fine the coding for each colour i.e red and yellow.

[jasoncw]

In the future, please be sure to use the [ CODE ] and [ / CODE ] tags around your code (without the spaces). Here is the slight amendment to your procedure that will include the result in cell C39:

Sub CountCells()
    Dim cell As Range
    Dim i As Long
    
    For Each cell In ActiveSheet.UsedRange
        If cell.Interior.ColorIndex = 3 Then _
            i = i + 1
    Next cell
    
    Range("C39") = i
End Sub

Jason

[bondi26]

Hi Jason,

thanks mate works a treat.

Andy