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​

 

[Special-K99]

"If G3:K3 is blue"

What do you mean by this?
G3:K3 contains the text "Blue"?
G3:K3 are coloured blue (Formulas cannot detect the colour of cells, this is only possible via VBA).
Or do you mean something else?

45 views and this is the only reply would suggest you need to explain things in more detail.

 

[jarhead58]

"If G3:K3 is blue"

What do you mean by this?
G3:K3 contains the text "Blue"?
G3:K3 are coloured blue (Formulas cannot detect the colour of cells, this is only possible via VBA).
Or do you mean something else?

45 views and this is the only reply would suggest you need to explain things in more detail.

Yep, clarity would def be better, sorry! They would be colored blue and VBA would be what I would need. A Module/function correct?

 

[Special-K99]

Yes, thats correct. Prob best to wait until a VBA expert comes along.

 

[Scott T]

What should happen if P3 is blue but the qty is < 2?

 

[jarhead58]

What should happen if P3 is blue but the qty is < 2?

If it is blue and G3:K3 >=2, it will affect what the G11 number will be, if it is blue by itself, it will put a value in G11. If it's not blue, it will have no effect on G11. It's almost like a powerball in lottery. I'm setting up a simple game for my grandkids; if it works properly lol! Let me know if you need more info! TIA

 

[Scott T]

In your original post you say "OR if P3 and G3:K3 is blue and qty is >=2, G11=this number" if P3 is blue but the number of blue cells in G3:K3 is under 2 should it be the same as if P3 is not blue? when you say some number do you want to generate a random number or hardcode a number?

 

[jarhead58]

In your original post you say "OR if P3 and G3:K3 is blue and qty is >=2, G11=this number" if P3 is blue but the number of blue cells in G3:K3 is under 2 should it be the same as if P3 is not blue? when you say some number do you want to generate a random number or hardcode a number?

The number will be hardcoded! Also, after looking at the"OR" statement I made, qty can be >=1; P3 being like a wildcard! Clear as mud right lol!

 

[jarhead58]

I'm recovering from major back surgery and trying to keep my brain active!
By the way Scott T​, I'm a big fan of Serenity!

 

[Scott T]

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