I am trying to trigger a worksheet change event when changes are made to a in-cell drop down list created via data validation. The change event works okay when the entry is typed into the cell however when it is selected from a list it does not work. Below is the code that I want to run when a cell in either column J or L of the target row is changed.
Any suggestions?
Code:
If Range("W" & target.Row).Value <> "" And Range("A" & target.Row).Value <> "" Then
If Application.WorksheetFunction.IsError(Range("AG" & target.Row)) = False Then
If Range("AH" & target.Row).Value > (Range("Q" & target.Row).Value _
+ Range("Q" & target.Row).Value * 0.2) And _
Range("W" & target.Row).Interior.ColorIndex <> 6 Then
ActiveSheet.Unprotect
Range("W" & target.Row).Activate
With ActiveCell.Interior
.ColorIndex = 6
End With
ActiveSheet.Protect
Response = MsgBox(prompt:="The application rate for this field provides significantly more Nitrogen than recommended by the University of Minnesota. " & vbNewLine & vbNewLine & "This application rate provides for a total of " & Range("AH" & target.Row).Value & " lbs of Nitrogen when combined with all nitrogen sources." & vbNewLine & "The University of Minnesota recommends " & Range("Q" & target.Row).Value & " lbs of Nitrogen for this crop after accounting for all" & vbNewLine & "Nitrogen Credits from previous crops." & vbNewLine & vbNewLine & "Do you still plan to apply manure at this rate for this field?", Buttons:=vbYesNo, Title:="Application Rate Provides more Nitrogen than Recommended")
If Response = vbNo Then
MsgBox prompt:="Please adjust the manure application rate highlighted in yellow." & vbNewLine & vbNewLine & "The cell will continue to be highlighted with yellow until an applicaiton rate that complies with the University of Minnesota recommendations is entered.", Buttons:=vbOKOnly, Title:="Correct the Application Rate"
End If
If Response = vbYes Then
MsgBox prompt:="The application rate identified for this field exceeds the 20% variation from the University of Minnesota recommendations allowed by Minnesota Feedlot Rules if site nutrient management history, soil conditions, or cool weather warrant additional nitrogen application." & vbNewLine & vbNewLine & " An allowable deviation from University of Minnesota recommendations is the use of the most recent nitrogen recommendation publication of a land grant college in a contiguous state. The Minnesota Feedlot Rules also allow for deviations when crop nitrogen deficiencies are visible or measured." & vbNewLine & vbNewLine & "If any of the above are the reasons for exceeding the University of Minnesota recommendations by more than 20% the reason must be documented and have a written explanation included with the Manure Management Plan.", Buttons:=vbOKOnly, Title:="Allowable Deviations from University of Minnesota Recommendations"
End If
End If
If Range("AH" & target.Row).Value <= (Range("Q" & target.Row).Value + Range("Q" & target.Row).Value * 0.2) Then
ActiveSheet.Unprotect
With Range("W" & target.Row, "X" & target.Row).Interior
.ColorIndex = 0
End With
ActiveSheet.Protect
End If
End If
End If
Any suggestions?