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]

Ok, so I figured out why it wasn't working! After setting True in the immediate window and "Manually" updating the B2:G2 range, it would work properly. Then when I would try it again, it wouldn't. Found that the events trigger never got reset to True after doing a trace. This code, at the bottom of the page kept the trigger at False!

''Sorts blank rows to the bottom as they occur
'
'    'Prevents endless loops
'    Application.EnableEvents = False
'    'They have more than one cell selected
'    If Target.Cells.Count >= 1 Then Exit Sub
'    If Target.Column <> 7 Then Exit Sub
'        If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then
'            Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _
'            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
'            Orientation:=xlTopToBottom
'    Application.EnableEvents = True
'        End If

After commenting it out, it would work all of the time when the numbers were entered "Manually" into the fields. Honestly, I've been working on this for so long, I can't remember why I have that code in there!

Now trying to use the basic form; where i just highlighted the labels and first row of data to create it, the event doesn't run; like you told me earlier; because it's not being told to! So now I need to create a custom form that will tell it to do that. Can I create it using the same range; B2:G2; without it screwing up my records? It's asking me to create the table with a range. Would that be the B2:G2 or ? Sorry, I've just always used the basic form tool.

 

[jarhead58]

Update on last post! Removed the comment out.

Fixed the event issue!

If Target.Cells.Count >= 1 Then "Added
Application.EnableEvents = True
Exit Sub

and an End If at the bottom!

 

[jarhead58]

Any pointers on the form creation?

 

[Scott T]

If all you need is to have it sort and count the colored cells then in the VBA editor right click on the button that you click to close the user form when you are finish. You will then need to add the code to sort and count cells.

 

[jarhead58]

With this code, I'm getting a"Method or data member not found. Is the UsedRange supposed to be DIM-ed? FYI, I'm using it as part of a click event! That's why the events are commented out. Also, it's going from newest to oldest so I believe the Order1 should be decending correct?

'Sorts blank rows to the bottom as they occur


    'Prevents endless loops
    'Application.EnableEvents = False
    'They have more than one cell selected
    If Target.Cells.Count >= 1 Then
     'Application.EnableEvents = True
     Exit Sub
    If Target.Column <> 7 Then Exit Sub
        If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then
            Me[COLOR=#ff0000].UsedRange[/COLOR].Sort Key1:=[A2], Order1:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom
    'Application.EnableEvents = True
        End If

 

[Scott T]

UsedRange is a property not a variable so you do not dim it.

Your code will always exit because target.cells.count will always be >=1. You are also missing the end if

If Target.Cells.Count >= 1 Then
     'Application.EnableEvents = True
     Exit Sub

You need to make this a multi line if statement like above so you can turn events back on.

If Target.Column <> 7 Then Exit Sub

Ascending puts the smallest number a the top, descending puts the larges number at top.

This code should work

'Prevents endless loops
Application.EnableEvents = False
 'They have more than one cell selected
 If Target.Cells.Count > 1 Then
    Application.EnableEvents = True
    Exit Sub
 End If
 If Target.Column <> 7 Then
    Application.EnableEvents = True
    Exit Sub
End If
 If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then
    Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Application.EnableEvents = True
 End If
 Application.EnableEvents = True ' just to make sure events get turned on again.
End Sub

 

[jarhead58]

Still getting that same error at the .UsedRange "Method or data member not found".

 

[Scott T]

The code works for me try changed the UsedRange to a range like below and see if it works. If it does then we know that UsedRange is causing the problem.

Me.Range("A2:D17").Sort Key1:=[A2], Order1:=xlAscending, _    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom

 

[jarhead58]

Same thing!

 

[Scott T]

In the VBA editor go to Tools/References what items are checked?