I was at the last hurdle of a program thanks to help from this group
until I hit a snag with the worksheet change procedure below. This
worked perfectly with the target ranges K12:K60 and J12:J60 being data
validation drop down combo boxes. Then because the list were very long I
introduced the worksheets procedure to create an auocomplete box shown
on the excellent site of Debra Dalgleish. This worked fine as far as the
autocomplete was concerned but the worksheet change procedure below was
not triggered. I tried an alternative in that K12:K60 changes a Vlookup
formula in the same rows, 12 to 60 in column L and J12:J60 changes
values in column M rows 12 to 60, so I made them the target range but as
they were changed by formula I presume the worksheet change was not
triggered. I then tried the worksheet calculate event but couldn't see
how to set up target ranges in that type of procedure. I would value any
solution to this situation.


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
n = Target.Row
If Cells(n, 11) = "Rough Grazing" Then
Cells(n, 15).Value = "No N"
Cells(n, 16).Value = "N"
Cells(n, 17).Value = "Rough Grazing"
Else
If UCase(Cells(n, 12)) = "GRASS" Then
Cells(n, 15).Value = "Low N"
Cells(n, 16).Value = "N"
Else: Cells(n, 15) = ""
Cells(n, 16) = ""
Cells(n, 17) = ""
End If
End If
End If
If Not Intersect(Target, Me.Range("J12:J160")) Is Nothing Then
n = Target.Row
If Cells(n, 10) = "Rough Grazing" Then
Cells(n, 14).Value = ""
Cells(n, 20).Value = "No N"
Else
If UCase(Cells(n, 13)) = "GRASS" Then
Cells(n, 14).Value = ""
Cells(n, 20).Value = "Low"
Else: Cells(n, 20) = ""
Cells(n, 23) = ""

End If
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

Kind Regards
Graham Haughs
Turriff, Scotland