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:
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?
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
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
 
Upvote 0
If you change both checks of color to 0 does the code work?
Code:
DisplayFormat.Interior.Color = 0
 
Upvote 0
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.

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
 
Upvote 0
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.

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?
 
Upvote 0
This range should be the cells they could be blue and you want to count.
Code:
[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
Code:
[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]
 
Upvote 0
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'.
 
Upvote 0
Thats all that the function is supposed to do, individual cells correct? Evidently, it ignores the range, above my knowledge lol!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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