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
4										Blue 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
11	Some Num

<tbody>
</tbody>

Sheet2​

 

[jarhead58]

Hmm, still not doing it. I put the code in the immediate window. Just for giggles, I even changed the range from "B2:G2", where I enter info via form, to "V14:AA14", where it changes cell color, still nothing. I also saved and restarted excel and the project.

FYI the code is in the "Worksheet" "Change" window!

 

[Scott T]

Put this in a module and run it. Do you get true?

Sub events()
If Application.EnableEvents = False Then
    MsgBox "FALSE"
Else: MsgBox "TRUE"
End If
End Sub

 

[jarhead58]

Yes

 

[Scott T]

If events are enabled then the code is running but not doing what you want.
Put stop at the start of the code this will stop the code from running. Then you can step though the code (press F8 to go to the next line of code) and see where the code goes the line about to run will be highlighted in yellow. Does the code execute like you expect? That is when a cell that is not in the range B2:G2 is changed it should jump down to the End If right before your sort code. If the cell changed is in the range it should run the count cells code.

Private Sub Worksheet_Change(ByVal Target As Range)

[COLOR=#FF0000]Stop[/COLOR]
If Not Intersect(Target, Range("B2:G2")) Is Nothing Then
Application.EnableEvents = False

 

[jarhead58]

Put the Stop in but it evidently isn't getting to the code! I saved and restarted again, put in new info via the form but nothing. One thing I forgot about was the actual change doesn't happen until I do the Newest to oldest sort to bring it to B2:G2. Not sure if that makes a difference or not.

 

[Scott T]

The code should always run when there is a change to the sheet. It may not do anything if the conditions but it should run. What sheet is the code on and what sheet is the range B2:G2 that you want to check on?

Is the sort the one in your change event or is it different code?

 

[jarhead58]

The code should always run when there is a change to the sheet. It may not do anything if the conditions but it should run. What sheet is the code on and what sheet is the range B2:G2 that you want to check on?

Is the sort the one in your change event or is it different code?

It's all on Sheet1 and I just do a manual Sort after closing the form!

 

[Scott T]

Sorting does not trigger the change event. You should be able to add code to your user form to sort on close and then run the count colors code.

You can use the macro recorder to record the sort and then add the code to you user form. It may require some tweaks if the range is dynamic.

 

[jarhead58]

The form is just the basic form mode that just gives you input boxes, previous record, next, etc., nothing customized.

 

[Scott T]

You would have to add the code to the form or you can run the count color code manual after sorting the spread sheet.