Macro - Pop Up Box - modification request

[SVTF]

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.

[SVTF]

Sorry looks as though I had the code tags in the wrong spot - it appears to be correct to me now.
It was a copy and paste error on my part.

Anyone have any thoughts on how to get my end result?

[lokicl]

Hi,

Use the below formulas in Column L and M respectively.


=IF(G2="N/A-Must add Comment",0,IF(I2="",IF(B2="","",$B$2-B2),I2-B2))
=IF(G2="N/A-Must add Comment","N/A",IF(I2=0,"N/A",IF(I2-B2<0,"N/A",I2-B2)))

Hope it works as your expectations.

Regards,
lokicl

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[SVTF]

@lokicl .. Thank you the above formulas return the desired result without interfering with the macro code.

One more part to work on...

Now all I need is if G ="N/A-Must add Comment" ... I need column I same row to be populated with N/A.

Could the code around the N/A pop up be modified to incorporate this?

Thoughts?

[lokicl]

Hi,

I have tweaked the code. Check this works out.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r   As Long
    Dim Msg As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        If Target.Cells.Count > 1 Then
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            Exit Sub
        End If
        
        If Target.Row < 2 Then
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            Exit Sub
        End If
        
        If Intersect(Target, Range("G:G,I:I")) Is Nothing Then
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            Exit Sub
        End If
        If IsEmpty(Target) Then
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            Exit Sub
        End If
        
        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
            Cells(r, "I") = "N/A"
            Msg = "Reminder Must add comment to Column J Row " & r
            Cells(r, "J").Select
        End If
              
        
         
            
Finished:
        If Msg <> "" Then MsgBox Msg, vbExclamation
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

regards,
lokicl

[SVTF]

Woot Woot .. Awesome .. Appears to be doing exactly what I need it to do for all elements of the code and formula's.

Thank You to @Leith Ross and @judgeh59 for the base macro code.

Thank You to @judgeh59 for multiple tweaks.

Thank You - Thank You to @lokicl for the final tweak that provided my with the result I was looking for.

@lokicl as soon as I can will send more rep.

You all rock!