# Help with Counta and Worksheet_Change Event

#### zoog25

##### Active Member
Hello All,

Here is my code i'm having issues with:
Rich (BB code):
``````Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

Set R = Intersect(Target, Range("K:Y"))
If Not R Is Nothing Then
For Each Cell In R
If Cell.Value <> "" Then
If Cell(Target, "A") = "PUB" Then
If Cell.CountA("K:V") = 12 Then
MsgBox ("NOC Items Completed")
End If
End If
End If
Next Cell
End If

End Sub``````
The issues i'm having is with Cell(Target, "A") and then Cell.CountA. With this situation with any changes made between column K and V. It first checks to see if column A says "PUB", if it does then it counts all the filled cells between K and V. If the count comes to 12 then the message box appears. Otherwise nothing happens. I have been debugging and the red is what is giving me errors. How do i fix it so it check column A and then counts cells between K and V. Thank you for any assistance that you can provide.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### jim may

##### Well-known Member
Where you have
If Cell(Target, "A")...
Where you have "Target" - Excel expects a numeric value(row number) . Target is a Range, not a numeric value...

You might try. If Cell(Target.row, "A")...
Hth,
Jim

Last edited:

#### Akuini

##### Well-known Member
Try this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

[COLOR=Royalblue]If[/COLOR] Target.Count > [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"K:V"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
[COLOR=Royalblue]If[/COLOR] Cells(Target.Row, [COLOR=brown]"A"[/COLOR]) = [COLOR=brown]"PUB"[/COLOR] [COLOR=Royalblue]Then[/COLOR]
[COLOR=Royalblue]If[/COLOR] WorksheetFunction.CountA(Cells(Target.Row, [COLOR=brown]"K"[/COLOR]).Resize(, [COLOR=crimson]12[/COLOR])) = [COLOR=crimson]12[/COLOR] [COLOR=Royalblue]Then[/COLOR]
MsgBox ([COLOR=brown]"NOC Items Completed"[/COLOR])
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]``````

#### DanteAmor

##### Well-known Member
Try:

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("K:V")) Is Nothing Then
If Cells(Target.Row, "A") = "PUB" And WorksheetFunction.CountA(Range("K" & Target.Row & ":V" & Target.Row)) = 12 Then
MsgBox ("NOC Items Completed")
End If
End If
End Sub``````

#### zoog25

##### Active Member
Thanks guys, the above items worked. It's taken me a week to figure it out, so thank you for helping me narrow the info done.