Macro - Pop Up Box - modification request

Hello,

Excel 2010

I have tweaked and re tweaked the code in this test sheet to where I can almost see the light at the end of the tunnel.

It is 90% where I need it to be.

Without interfering with any of the code below .. just need to add another condition for the N/A pop up part of the macro.

You may or may not need to know but there are formulas in L and M - this may be important when you see my request.

In column L there is the following formula =IF(I2="",IF(B2="","",$B$2-B2),I2-B2)
In column M there is the following formula =IF(I2=0, "",IF(I2-B2 1 Then Exit Sub

  
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r   As Long
    Dim Msg As String
    
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Row < 2 Then Exit Sub
        If Intersect(Target, Range("G:G,I:I")) Is Nothing Then Exit Sub
        If IsEmpty(Target) Then Exit Sub
        
        r = Target.Row
            
        If Cells(r, "G") = "No" And IsDate(Cells(r, 9)) Then
            Msg = "In Row " & r & " Column G response is No - Change to Yes"
            Cells(r, "G").Select
            GoTo Finished
        End If
                           
        If Cells(r, "G") = "Yes" And Not IsDate(Cells(r, 9)) Then
            Msg = "Reminder add a correction date in Column I " & r
            Cells(r, "I").Select
        End If
        
        If Cells(r, "L") > 14 And IsDate(Cells(r, 9)) Then
            Msg = "Add comment to Column J Row " & r & " resolution > 14 days"
            Cells(r, "J").Select
        End If
        
        If Cells(r, "G") = "N/A-Must add Comment" And Not IsDate(Cells(r, 9)) Then
            Msg = "Reminder Must add comment to Column J Row " & r
            Cells(r, "J").Select
        End If
              
        
         
            
Finished:
        If Msg <> "" Then MsgBox Msg, vbExclamation
        
End Sub

Here is what I am looking for:

#1 - When the data validation list in column G is used to select "N/A-Must add comment"

• I would like column I (same row) to populate with N/A and the result in L & M be 0 and N/A respectively (keep formulas intact) (or some other version with same result)
• And continue to have the pop up work as it does now (only when N/A is selected in G - pop up comes up to remind to add comment to J)

So not sure if we need to modify the code and the formula's in L & M to get the desired end result.

Please help me but this thing to bed - It would be greatly appreciated

I have attached a workbook.


Thanks again for looking.