Counting coloured cells

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
348
Office Version
  1. 2016
Platform
  1. Windows
Hey all,

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

GHIJKLMNOP
4Blue or not

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
11Some Num

<tbody>
</tbody>
Sheet2
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
"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.
 
Upvote 0
"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?
 
Upvote 0
What should happen if P3 is blue but the qty is < 2?
 
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
Try, change the numbers to what you want

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("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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top