Worksheet Change event problem

[Graham Haughs]

Please excuse the crudity off the change procedure below but it is "just
about" doing what I want it to do but I just cannot get that extra input
need to make it work. At the moment it will enter "No N" in column 14
when I change a value in the relevant area in column K The trouble is
that It will enter "No N" anywhere in the column providing there is
"Grass" in column 13. What I want is the value to change only in the
same row on which the change is made in column K and the other rows to
stay unchanged unless another change is made in K on the relevant row. I
value any help.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("K12:K160")) Is Nothing Then
With Target
For n = 12 To 160
If Cells(n, 13) = "Grass" Then
Cells(n, 14).Value = "No N"
Else: Cells(n, 14) = ""
End If
Next n
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Kind regards
Graham Haughs
Turriff
Scotland

 

[Don Guillett]

try
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("K12:K160")) Is Nothing Then

' With Target
'For n = 12 To 160
'If Cells(n, 13) = "Grass" Then

n=target.row
if ucase(target)="GRASS" then
Cells(n, 14).Value = "No N"
Else
Cells(n, 14) = ""
End If

' Next n
' End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

 

[Graham Haughs]

Thanks for that Don but getting no entries of "No N" in column N at all
now. A bit confused with < if ucase(target)="GRASS" then > as text
"Grass" is in column N. Appreciate your input however and will persevere
with what you sent.

Graham

 

[Graham Haughs]

Don, this is a rapidly following on apology for the text below. I see
what you have done now, my slower brain just took a bit of catch up
time. many thanks for your help.

Graham

 

[Don Guillett]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("K12:K160")) Is Nothing Then
' With Target
'For n = 12 To 160
n = Target.Row
If UCase(Cells(n, 13)) = "GRASS" Then
Cells(n, 14).Value = "No N"
Else: Cells(n, 14) = ""
End If
' Next n
' End With
End If
ws_exit:
Application.EnableEvents = True
End Sub