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.
<tbody>
</tbody>
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.
Any help will be greatly appreciated.
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.
M | N | O | P | |
3 | 6/6/18 | Yes | Apple | =COUNTIF(M3:O3,"") |
4 | 7/6/18 | Yes | =COUNTIF(M4:O4,"") | |
5 | Yes | Orange | =COUNTIF(M5:O5,"") | |
to 500 | ||||
<tbody>
</tbody>
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.
Code:
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.