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​

 

[RoryA]

So the button is on the worksheet, not the userform?

 

[jarhead58]

Awe man, I've been feeding false info, what an idiot. The button is on the form code! I think I'll go hide under a rock!

 

[jarhead58]

Ok, now that I have thoroughly embarrassed myself!! Code is on the form code, "Private Sub NewRec_Click". This is what I have in there as of now! I used Activate.Range("A2") instead of Me.UsedRange and it then errors with the Run-time '424' Object required!

Private Sub NewRec_Click()

Dim lastrow As Long
Dim Ctrl As Control
    lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Cells(lastrow + 1, "A").Value = DateBox.Text
    Cells(lastrow + 1, "B").Value = Ball1.Text
    Cells(lastrow + 1, "C").Value = Ball2.Text
    Cells(lastrow + 1, "D").Value = Ball3.Text
    Cells(lastrow + 1, "E").Value = Ball4.Text
    Cells(lastrow + 1, "F").Value = Ball5.Text
    Cells(lastrow + 1, "G").Value = Power.Text
    Cells(lastrow + 1, "H").Value = PowerPlay.Text
    
    'Prevents endless loops
Application.EnableEvents = False
 'They have more than one cell selected
[COLOR=#ff0000] If Target.Cells.Count >= 1 Then[/COLOR]
    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
    ActiveSheet.Range("A2").Sort Key1:=[A2], Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Application.EnableEvents = True
 End If
 
                 'Clears Form and returns to 1st textbox
For Each Ctrl In Me.Controls
    If TypeName(Ctrl) = "TextBox" Then
        Ctrl.Value = ""
    End If
Next Ctrl
DateBox.SetFocus
 Application.EnableEvents = True ' just to make sure events get turned on again.
End Sub

 

[Scott T]

You can not use target like this in the form code.

When you do a event triggered code like a change event the variable is automatically created and set to the cell(s) that are changing.


With user form code that is not happing. If you still want to use target you will need to Dim it as a range and then set it to some thing. What do you want to do? you may not need any of the IFs.

To sort every time the button is clicked this should work

Private Sub NewRec_Click()
Dim lastrow As Long
Dim Ctrl As Control
    lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Cells(lastrow + 1, "A").Value = DateBox.Text
    Cells(lastrow + 1, "B").Value = Ball1.Text
    Cells(lastrow + 1, "C").Value = Ball2.Text
    Cells(lastrow + 1, "D").Value = Ball3.Text
    Cells(lastrow + 1, "E").Value = Ball4.Text
    Cells(lastrow + 1, "F").Value = Ball5.Text
    Cells(lastrow + 1, "G").Value = Power.Text
    Cells(lastrow + 1, "H").Value = PowerPlay.Text
    

    ActiveSheet.Range("A2").Sort Key1:=[A2], Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
 
                 'Clears Form and returns to 1st textbox
For Each Ctrl In Me.Controls
    If TypeName(Ctrl) = "TextBox" Then
        Ctrl.Value = ""
    End If
Next Ctrl
DateBox.SetFocus
End Sub