Looking to count cell based on their value & if they are not filled with a color

[Phantasm]

So basically what I have is a project management worksheet that I would like to get how many saw jobs I have that are not done.

What I have is a part number say in A1. B1:N1 would be all the operations that the job needs to go through, like saw, laser, bp, mill, bend, ...When an operation is complete, I fill the cell with green.

In O1, I have the value "SAW". In O2, I would like to count the instances of the value of O1 ("SAW") where the cells are not filled with a color in the range of B1:N200.

I would like to do this for each operation (mill, bp....)

Is this possible? I hope I explained this well enough.

 

[Joe4]

Sorry, brain cramp this morning. Had not gotten my caffeine in me, and forgot the parameters are coming from the UDF.
Let's start with this. What is the maximum number of colors that you want to check for?
Or can we approach it a different way and have it check for ANY coloring?

 

[Phantasm]

Sorry, brain cramp this morning. Had not gotten my caffeine in me, and forgot the parameters are coming from the UDF.

Or can we approach it a different way and have it check for ANY coloring?

The max number of colors would be 4.

I would like to also see the other option with any coloring too.

 

[Joe4]

I would like to also see the other option with any coloring too.

This is pretty easy, like this:

Function CheckCellColor(strWord As String, rng As Range) As Long
'   Counts the number of cells in the range matching the word, but without any color
'       strWord = string to look for
'       rng = range to search
'
        Dim cell As Range

        For Each cell In rng
            If (cell = strWord) And (cell.Interior.Pattern = xlNone) Then
                CheckCellColor = CheckCellColor + 1
            End If
        Next cell

End Function

So, the formula would look like:
=CheckCellColor(O1,B1:N200)

The other one is more difficult and will take some time.

 

[Joe4]

OK, I got code that can do up to 4 colors:

Function CheckCellColors(strWord As String, rng As Range, _
                        cCol1 As Double, Optional cCol2 As Double, Optional cCol3 As Double, Optional cCol4 As Double) As Long
'   Counts the number of cells in the range matching the word, but without the color designated
'       strWord = string to look for
'       rng = range to search
'       cColn = codes of colors to exclude
'
'       * you can get the cell color code by using the Macro Recorder, recording yourself
'         coloring a cell, and them see what code is recorded
        
    Dim cell As Range
    Dim crit
    Dim i As Long
    Dim clr As Double
    Dim bl As Boolean
        
'   Store all four colors in an array
    crit = Array(cCol1, cCol2, cCol3, cCol4)

'   Loop through cells in range
    For Each cell In rng
'       Set defaults
        bl = True
'       Check for word
        If cell = strWord Then
'           Loop through colors
            For i = LBound(crit) To UBound(crit)
                clr = crit(i)
                If (clr > 0) And (cell.Interior.Color = clr) Then
                    bl = False
                    Exit For
                End If
            Next i
        Else
            bl = False
        End If
'       Determine whether or not to count
        If bl Then CheckCellColors = CheckCellColors + 1
    Next cell

End Function

So, if you were checking one, it might look like:

=CheckCellColors(O2,$G$1:$N$200,4697456)

If you were checking 4, it would look something like:

=CheckCellColors(O2,$G$1:$N$200,4697456,65535,5287936,255)

 

[Phantasm]

Thank you very much!

The last question I have is, I noticed it will not recalculate when a cells color is changed. It will only recalculate if a lookup value is added or deleted. It wont even recalculate if you change a cell color & hit F9. Is this just a limitation of that function not being part of the software?

 

[Joe4]

That is because it is data changes that trigger the recalculation in Excel formulas (native or VBA). Changing the color (or any other formatting) of a cell does not trigger a recalculation.
If you go to the formula, and re-enter it (hit F2, then Enter), it will recalculate it.