Unsuccessful Worksheet.Change with Target Range

[Nadine67]

Hello and thank you for any attention my post may receive.

I have a worksheet that has one target range (P3:P500) containing formulas where if the cell in this column on the active row calculates to 3 then a message box will appear so that the user can decide to either cancel, or OK to execute further code. All instances of 0 on non-active rows is ignored.

Example -
if row 3 is the active row and P3 = 0 the a message box will appear.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6/6/18[/TD]
[TD]Yes[/TD]
[TD]Apple[/TD]
[TD]=COUNTIF(M3:O3,"")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]7/6/18[/TD]
[TD]Yes [/TD]
[TD][/TD]
[TD]=COUNTIF(M4:O4,"")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Orange[/TD]
[TD]=COUNTIF(M5:O5,"")[/TD]
[/TR]
[TR]
[TD]to 500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am confused as to what code I need to execute to successfully achieve my goal. I used this code (unsuccessfully) which I found on the net but do not know how to tweak it to look at only the 'P'-cell in the active row.

Private Sub Worksheet_Calculate()
Static oldval
If Range("P3").Value = 0 Then
    oldval = Range("P3").Value
    If MsgBox("You have entered information in all three mandatory fields." _
    & vbNewLine & vbNewLine & "If you are sure the information is correct and would like to close this Action, please select 'OK'." _
    & vbNewLine & vbNewLine & "Otherwise select 'Cancel' to keep this Action open; by the way this will clear the 'Closure Date' field.", vbOKCancel) = vbCancel Then Range("M3").ClearContents
End If
End Sub

Any help will be greatly appreciated.

 

[jproffer]

See if this does what you want. I took out the "oldvalue" line for testing purposes, and because you didn't use it (at least in this routine). If you're using it elsewhere, add it back in....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M3:O500")) Is Nothing Then
    If Range("P" & Target.Row).Value = 0 Then
        If MsgBox("You have entered information in all three mandatory fields." _
        & vbNewLine & vbNewLine & "If you are sure the information is correct and would like to close this Action, please select 'OK'." _
        & vbNewLine & vbNewLine & "Otherwise select 'Cancel' to keep this Action open; by the way this will clear the 'Closure Date' field.", vbOKCancel) = vbCancel Then Range("M" & Target.Row & ":O" & Target.Row).ClearContents
    End If
End If
End Sub

 

[Nadine67]

**** SOLVED ****

Thank you jproffer. Your solution has solved my issue and reached my goal.

Have a great day!

 

[David Luiz]

Thanks for the help