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]

Is the code in a userform (would explain the error) or a worksheet code module?

 

[jarhead58]

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

VB for apps
Microsoft Excel 16.0 Obj. Lib
OLE Automation
Microsoft Off. 16.0 Obj. Lib
Microsoft Forms 2.0 Obj. Lib

 

[jarhead58]

In a code module.

 

[RoryA]

It has to be in a code module (it is code, after all ), I'm asking which one - worksheet or userform?

 

[jarhead58]

It has to be in a code module (it is code, after all ), I'm asking which one - worksheet or userform?

Oops! My bad! It's in a worksheet module.

In the meantime, I went back to the UsedRange and changed Me to ActiveSheet. It seems to have gotten thru but then got a Run-time error '424' at this point in the code:

'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
    ActiveSheet.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

 

[RoryA]

If it's in a worksheet module, you shouldn't get an error on Me.Usedrange since all worksheets have a Usedrange property.

Also, please use CODE tags, not QUOTE tags, when posting code.

 

[jarhead58]

If it's in a worksheet module, you shouldn't get an error on Me.Usedrange since all worksheets have a Usedrange property.

Also, please use CODE tags, not QUOTE tags, when posting code.

Again, apologies!

It's happening as soon as I 'click' my button to get the info on the sheet. It stops at "Private Sub NewRec_Click()" and highlights it in yellow along with the ".UsedRange" in blue. So it's not even starting the Sub yet!

P.S. I did change it back to Me.UsedRange; causing this last error, not the Run-time error now!

 

[jarhead58]

I've been so caught up in trying to get this thing working that I didn't even notice that I was working with someone else now! Just a shot in the dark here; does the fact that I don't have a loop to clear the form after putting it into the sheet?? I am an "old" noob at this but wanting to learn and understand whats going on!

 

[Scott T]

Is this code for the button on your user form? If so Me would cause problems as it is not the worksheet, which is what you want the code to act on.

You will also have a problem with using Target as it will be nothing. Some workbook events like a Change event will have

Private Sub Worksheet_Change(ByVal Target As Range)

Target will be the cell that was changed if you are using a event that does not have a target or a button then there is no target for the code to use.

 

[jarhead58]

Is this code for the button on your user form? If so Me would cause problems as it is not the worksheet, which is what you want the code to act on.

You will also have a problem with using Target as it will be nothing. Some workbook events like a Change event will have

Private Sub Worksheet_Change(ByVal Target As Range)

Target will be the cell that was changed if you are using a event that does not have a target or a button then there is no target for the code to use.

No sir, it is on the worksheet code module, "Private Sub NewRec_Click()".