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]

Excel formulas can only work of the values in the cells, then do not work off of color or other formatting you may have applied to the cells.
In order to do something like that, that would require VBA.
If you are open to a VBA solution, please let us know.
Otherwise, it might be better that instead of coloring a cell green, you have some cell that you toggle with the value "Complete" or something like that. then you could use a simple COUNTIF formula to get what you want.

 

[Phantasm]

Im open to a VBA solution, but can it be coded into a command like "SUM"...What Im saying is, can I code a command that is called "CHECKCELLCOLOR"...cell O1 would contain the word "SAW" in cell O2, I would type in =CHECKCELLCOLOR() and it would reference the value of cell O1 & count how many times it finds a sell with that value that is not filled with color. So it would pretty much work as a COUNTIF.

Thanks.

 

[Joe4]

OK, try this User Defined Function in VBA:

Function CheckCellColor(strWord As String, rng As Range, cellColor 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
'       cellColor = code of color 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

        For Each cell In rng
            If (cell = strWord) And (cell.Interior.Color <> cellColor) Then
                CheckCellColor = CheckCellColor + 1
            End If
        Next cell

End Function

So, the formula we put in a cell might look something like this:

=CheckCellColor(O1,B1:N200,5287936)

 

[Phantasm]

OK, try this User Defined Function in VBA:

Function CheckCellColor(strWord As String, rng As Range, cellColor 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
'       cellColor = code of color 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

        For Each cell In rng
            If (cell = strWord) And (cell.Interior.Color <> cellColor) Then
                CheckCellColor = CheckCellColor + 1
            End If
        Next cell

End Function

So, the formula we put in a cell might look something like this:

=CheckCellColor(O1,B1:N200,5287936)

Thank you. It looks like it works, but Im having a problem with the color number. When I do as you suggested, I get the following:

Range("N37").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Not sure how to get this into a number...

 

[Joe4]

You can run a simple line of code like this to get the color code:

Sub GetColor()
    MsgBox Range("N37").Interior.Color
End Sub

 

[Phantasm]

Everything works great. I cant thank you enough!

 

[Phantasm]

One last question...If I wanted to edit the command to check for multiple different cell colors, say green and red, what would I change?

 

[Joe4]

Just keep adding more ANDs, checking for the other colors, i,e,

If (cell = strWord) And (cell.Interior.Color <> cellColor) And (cell.Interior.Color <> cellColor) Then

 

[Phantasm]

That dosent seem to work...My command looks like:

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

and its giving me a #Value ! error

Wouldnt you need an OR statement to check to see if it is one of two values?