Counting coloured cells

[jarhead58]

Hey all,

Title says it all! Here's what I'm trying to do! Let me know if this can be done! TIA!!

	G	H	I	J	K	L	M	N	O	P

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0F6FC6]#0F6FC6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0F6FC6]#0F6FC6[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0F6FC6]#0F6FC6[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Blue or not[/TD]

</tbody>

Sheet2​

​

If G3:K3 is blue count it. If qty is 3, G11=a number, 4=another number…
OR if P3 and G3:K3 is blue and qty is >=2, G11=this number

	G
Some Num

<tbody>
[TD="align: center"]11[/TD]

</tbody>

Sheet2​

 

[jarhead58]

Disregard, I didn't isolate the Sub findcolor () but it returned "0" in the message box. In order to run this mod automatically after B2:G2 are updated via form mode, should it be in an event trigger instead of as a normal sub?

 

[Scott T]

0 is black, did you change the cell to point to a cell that blue?

To run the code automatically if the value of B2:G2 change it would have to be Worksheet_Change event. It is very easy to do that once the code is working as intended.

 

[jarhead58]

0 is black, did you change the cell to point to a cell that blue?

To run the code automatically if the value of B2:G2 change it would have to be Worksheet_Change event. It is very easy to do that once the code is working as intended.

Can I use a range; M12:R12 or only one at a time because P12 is blue but still getting black

 

[Scott T]

If you change both checks of color to 0 does the code work?

DisplayFormat.Interior.Color = 0

 

[jarhead58]

If you change both checks of color to 0 does the code work?

DisplayFormat.Interior.Color = 0

Color is "0"

 

[Scott T]

Assuming you have no cells filled with black in M12:Q12 then run this code.
Does this code return a number in W11? If it does then that is why the prior code did not return a number non of the cells color value matched what was in the code.

Sub ccolor()
Dim cell As Range
Dim mycount As Long
'I assume you want to clear G11 if not you can remove the line below.
Range("W22").ClearContents
For Each cell In Range("M12:Q12")
    If cell.DisplayFormat.Interior.Color = 0 Then
        mycount = mycount + 1
    End If
Next cell
If Range("P3").DisplayFormat.Interior.Color = 0 And mycount >= 1 Then
    Range("W11") = 77
Else
    Select Case mycount
        Case 0
        Range("W22") = 0
        Case 1
        Range("W22") = 1
        Case 2
        Range("W22") = 2
        Case 3
        Range("W22") = 3
        Case 4
        Range("W22") = 4
        Case 5
        Range("W22") = 5
    End Select
End If
End Sub

 

[jarhead58]

Assuming you have no cells filled with black in M12:Q12 then run this code.
Does this code return a number in W11? If it does then that is why the prior code did not return a number non of the cells color value matched what was in the code.

Sub ccolor()
Dim cell As Range
Dim mycount As Long
'I assume you want to clear G11 if not you can remove the line below.
Range("W22").ClearContents
For Each cell In Range("M12:Q12")
    If cell.DisplayFormat.Interior.Color = 0 Then
        mycount = mycount + 1
    End If
Next cell
If Range("P3").DisplayFormat.Interior.Color = 0 And mycount >= 1 Then
    Range("W11") = 77
Else
    Select Case mycount
        Case 0
        Range("W22") = 0
        Case 1
        Range("W22") = 1
        Case 2
        Range("W22") = 2
        Case 3
        Range("W22") = 3
        Case 4
        Range("W22") = 4
        Case 5
        Range("W22") = 5
    End Select
End If
End Sub

Ok, maybe I misunderstood what you were doing. M12:Q12 are normally Black R12 is normally Red. I didn't make any changes yet with your above code. Did I misunderstand?

 

[Scott T]

This range should be the cells they could be blue and you want to count.

[COLOR=#333333][I]For Each cell In Range("M12:Q12")[/I][/COLOR]

use the other sub posted to get the number of the color you have and replace the 0s with that number

[COLOR=#333333][I]  If cell.DisplayFormat.Interior.Color = 0 Then
[/I][/COLOR]If Range("P3").DisplayFormat.Interior.Color = 0 And mycount >= 1 Then[COLOR=#333333][/COLOR]

 

[jarhead58]

Using the findcolor, it returns '0' for all cells, even the blue ones. It's not working for the conditional format which turns it blue. Even when changing the '0' to '12611584'.

 

[jarhead58]

Thats all that the function is supposed to do, individual cells correct? Evidently, it ignores the range, above my knowledge lol!