Count by color only if another condition holds

[g579]

Hi,



I've been using a user defined function that I found in the forum below and I would like to manipulate it to make more use out of it and I was wondering if you know how to do this. What I am trying to do is basically count the number of cells that are green in one column only if the corresponding cell in another column contains a certain text like "Office".

Basically, I only want to count the green cells in say column A if the corresponding cell in column B contains "Office". I also would like it to refresh automatically every time a change happens.

Thank you in advance for your help!!

Public Function CountIfColor(rng As Range, clrindx As Integer)
Dim Cell
CountIfColor = 0
For Each Cell In rng.Cells
If Cell.Interior.ColorIndex = clrindx Then
CountIfColor = CountIfColor + 1
End If
Next Cell
End Function

To return the quantity of red-shaded (non-conditionally formatted) cells in the range A1:A100 for example, this is the formula you'd type into a cell:

=CountIfColor(A1:A100,3)

 

[Norie]

Try this.

Public Function CountIfColor(rng As Range, clrindx As Long) As Long
Dim cl As Range
 
    Application.Volatile
    
    For Each cl In rng.Cells
        If cl.Interior.ColorIndex = clrindx And cl.Offset(, 1) = "Office" Then
            CountIfColor = CountIfColor + 1
        End If
    Next cl
    
End Function

By the way is it green or red?

 

[g579]

Thanks Norie.

That actually didn't work. Did I have to add it to the previous code or start a new one? I started a new one and it came back with a #VALUE Error.

The function I inserted in the cell was =CountIfColor(Range,color index)


p.s. I'm looking up green cells I changed the color index accordingly

 

[g579]

Oh and btw the two columns that I am trying to compare are columns B and F. So there are three columns between them.

 

[Norie]

Well it worked fine for me.

You should replace the original code with it.

There could be all sorts of reasons for getting the #VALUE! error.

It could be something to do with the code, it could be the data, it could be the formatting.

Generally it's not a good idea to count by colour, though obviously it can be done.

 

[g579]

Ok I figured out the problem. It works now! you're a genius thank you soo much but now is there a way for it to refresh automatically once a change has been made?

 

[Norie]

Do you have anything else you want to mention?

Green/Red, columns B/F.

The code I posted should work for 2 adjacent range, with the leftmost, eg A, being the one with the formatting and the other, eg B, with the search term.

It's probably possible to adjust the code to work with non-adjacent columns, or even to take 2 ranges as arguments.

 

[g579]

Sorry, I didn't know it wouldn't refresh automatically or I would have mentioned that from the start.

I adjusted the column offset to 4 to account for the space between the columns.

Now, really the only thing is to make it refresh automatically but I don't know if that's even possible.

Thanks

 

[Norie]

Refresh automatically when exactly?

It seemed to refresh fine when I tried some simple tests.

eg copy/pasting, entering values etc

 

[g579]

I have it in my spreadsheet right now and if I make a format change to green in column B then it wouldn't update the count value automatically. It only updates it if I click in the cell where the formula is found and hit enter after the change.