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]

Try, change the numbers to what you want

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("G11").ClearContents
For Each cell In Range("G3:K3")
    If cell.Interior.Color = 12611584 Then
        mycount = mycount + 1
    End If
Next cell
If Range("P3").Interior.Color = 12611584 And mycount >= 1 Then
    Range("G11") = 77
Else
    Select Case mycount
        Case 0
        Range("G11") = 0
        Case 1
        Range("G11") = 1
        Case 2
        Range("G11") = 2
        Case 3
        Range("G11") = 3
        Case 4
        Range("G11") = 4
        Case 5
        Range("G11") = 5
    End Select
End If
End Sub

Is this going in the Worksheet/Activate page?

 

[Scott T]

This can go into a module but if you want to use an event to trigger the code then it would have to go on the worksheet page and be change to trigger on the event.

 

[Rick Rothstein]

This can go into a module but if you want to use an event to trigger the code then it would have to go on the worksheet page and be change to trigger on the event.

As a follow up...

No UDF (user defined function) or event code can respond to a user changing the color of a cell... the count will remain incorrect after a color change until after the worksheet recalculated for a UDF or the event procedure you put the code in executes due to a change that that particular event can "see".

 

[jarhead58]

This can go into a module but if you want to use an event to trigger the code then it would have to go on the worksheet page and be change to trigger on the event.

Ok, this is a copy of your code with the real cell locations. Also, I neglected to mention, the color of the cell will change based on input value from B2:F2 for the M12:Q12 and input from G2 for R12(guessing it could be B2:G2 input for the M12:R12??). Anyhow, numbers are entered into B2:G2 which are checked against M12:R12 turning matching cells to blue! I'm sorry about forgetting to mention that in the beginning, pain pills lol!

'I assume you want to clear W22 if not you can remove the line below.
Range("W22").ClearContents
For Each cell In Range("M12:Q12")
If cell.Interior.Color = 12611584 Then
mycount = mycount + 1
End If
Next cell
If Range("R12").Interior.Color = 12611584 And mycount >= 1 Then
Range("W22") = 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

 

[Scott T]

What cells are colored? Are the cells being colored using conditional formatting to change the fill color? If so the code needs to be change to account for this.

 

[jarhead58]

What cells are colored? Are the cells being colored using conditional formatting to change the fill color? If so the code needs to be change to account for this.

Yes, conditional formatting changes them.

 

[Scott T]

This will work with the cells formatted by conditional formatting.
the .Color = 12611584 is the blue is the blue from the standard colors if you use a different blue you will need to change the number to match. You can use this code to tell you the color of the cell just change M12 to the cell you want to test.

Sub findcolor()wcolor = Range("M12").DisplayFormat.Interior.Color
MsgBox ("the color is " & wcolor)
End Sub

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 = 12611584 Then
        mycount = mycount + 1
    End If
Next cell
If Range("P3").DisplayFormat.Interior.Color = 12611584 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]

This will work with the cells formatted by conditional formatting.
the .Color = 12611584 is the blue is the blue from the standard colors if you use a different blue you will need to change the number to match. You can use this code to tell you the color of the cell just change M12 to the cell you want to test.

Sub findcolor()wcolor = Range("M12").DisplayFormat.Interior.Color
MsgBox ("the color is " & wcolor)
End Sub

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 = 12611584 Then
        mycount = mycount + 1
    End If
Next cell
If Range("P3").DisplayFormat.Interior.Color = 12611584 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

Thanks, the first code is unnecessary, I don't need to display it in this project but I will keep it for future ref.
The second one is in the General page. I have 1 cell blue but W22 isn't displaying thee hard code.

 

[Scott T]

Run the first sub and make sure that number matches the color number in the second code. That is the first sub should return 12611584 if not the second sub will not see that the cells in M12:Q12 are colored blue. If they do not match change the second sub to match.

 

[jarhead58]

Run the first sub and make sure that number matches the color number in the second code. That is the first sub should return 12611584 if not the second sub will not see that the cells in M12:Q12 are colored blue. If they do not match change the second sub to match.

Ran the first sub but the first line goes red with an error,"Compile Error: Expected end of Statement" and wcolor is highlighted blue.