Count if cells have a color fill

[philobr]

Hi there,
I have a matrix and I need to do a count per row if cells have a color fill.

How can I do this?

Thanks

 

[JamesW]

Hi,

Try:

Sub countFill()
    lRow = Range("A1").End(xlDown).Row
    For i = 1 To lRow
        If Cells(i, 1).Interior.ColorIndex <> -4142 Then j = j + 1
    Next
    Range("B1").Value = j
End Sub

 

[Eurekaonide]

in 2007 you can use filters in the VBA code to find and count colours - but in lower versions you will need to use the color index and know the number of the colour you want.

 

[philobr]

I have Excel2003 and the color Im using is yellow.

I tried the macro but it doesnt seem to work.

What I need is to count the number of cells in each row (50 columns wide) which have a color (yellow in this case) and then put the answer in E2 down.

So I'll end up with something like this:
25
30
50
10
43

 

[JamesW]

Try:

Sub countFill()
    lRow = Range("A2").End(xlDown).Row
    lCol = Range("A1").End(xlToRight).Column
    
    For i = 2 To lRow
        For j = 1 To lCol
            If Cells(i, j).Interior.ColorIndex = 6 Then x = x + 1
        Next
        Cells(i, 5).Value = x
        x = 0
    Next
End Sub

 

[philobr]

That counted the cells, but put the answer from E2 and kept going in a loop all the way down the sheet.

Plus it had to be run, I couldnt just change the cell color as I went.

 

[JamesW]

I thought that's what you wanted?

and then put the answer in E2 down.

Not sure I fully understand what you require now.

 

[philobr]

Yes it works, but I dont need it to count every row.
If I change the color on 10 rows for example its still looping all the way down until I press ESC

Is it possible to set it to only run the count if a cell changes color
rather than having to run it multiple times?

 

[RoryA]

You would need a function, but changing the colour of a cell does not cause a recalculation so the best you could get would be to have the funciton update when the workbook does recalculate.
General note: colour is not data, and should not be used as such. Since no-one ever listens when I tell them that, have a look here for functions to sum/count by colour.

 

[JamesW]

Try this:

It will count the coloured cells on which ever row the activecell is on.

Sub countFill()
    lCol = Range("A1").End(xlToRight).Column
    cRow = ActiveCell.Row
    
        For j = 1 To lCol
            If Cells(cRow, j).Interior.ColorIndex = 6 Then x = x + 1
        Next
        Cells(cRow, 5).Value = x
End Sub

Hopefully this is a little more suitable!