Requiring popup message to run after 2 conditions are met.

[FrancisM]

I am trying to get a pop up message to run if the following 2 conditions are met "No" & "True". No is in cell range G4:G10, G13:G17; True is located in W4:W10, W13:W17. However true is a Boolean answer. I do have a formula located in cell range x4:x10, x13:x17 that concerts the Boolean answer to a 1 or 0. I thought that you had to convert a Boolean answer to a numeric answer to use in code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     'The code below is a reminder to enter data in the Referral Workbook.
     Application.ScreenUpdating = False
     Dim lastRow As Long
     Dim cell As Range
     lastRow = Range("G" & Rows.Count).End(xlUp).Row


For Each cell In Range("G4:G10, G13:g17" & lastRow)
    If InStr(1, cell.Value, "No") <> 0 Then
        If InStr(1, cell.Offset(, 17).Value, "1") <> 0 Then
        End If
    End If
Next


Application.ScreenUpdating = True
         MsgBox "Check to verify veteran data is entered in FY ## REFERALS." & vbCr & _
                "It's critical that the veteran data is captured." & vbCr & _
                "You have entered No into cell" & Target.Address, vbInformation, "Career Link Meeting List"
   
        Call Referals
    
  
 End Sub

When I run the code it sees the "no" & runs the message, & calls Referals. I have are 3 questions.
1.Do you have to convert Boolean answers to numeric?
2. How do you get excel to see the second condition?
3. Could you,explain what I am doing wrong?

 

[FrancisM]

Same result. No message. G is a data validation drop down, if that matte It should not.

I just noticed that column W is #22 not #16 . Sorry

 

[Fluff]

Can you re-supply the file, but upload to OneDrive & mark for sharing, rather than as an Excel-online file.

 

[FrancisM]

yes. I had to do it from home. will try in about 2 hours

 

[FrancisM]

Here is the link. Let me know if you have better luck.

https://1drv.ms/x/s!Ak-4iXjPpsJMgQWftoDv3XUG8oeG

 

[Fluff]

Unfortunately that is still opening in Excel Online. Therefore all unsupported content will get stripped out when I download it.

 

[FrancisM]

strange. I will see what I can do.

 

[FrancisM]

https://1drv.ms/x/s!Ak-4iXjPpsJMgQaWDCkk6ods82Vd

I noticed that even if I clicked on open in excel, it opens on line. Very strange. However looking @ my cell layout could you construct something on your end?
I think i figured it out. I clicked on open in excel, open url:excel prtoocol. It asked if I wanted to open the url I posted, & said yes.
I remembered the first time I did this it asked for my email & password. Just like i was signing into one drive. tTy that.

 

[Fluff]

Ok, that time it left most of the stuff intact, change this

    Select Case Sh.Name

to

    Select Case Sh.CodeName

and this

     If Target.Value = "No" And Target.Offset(, 22).Value = True Then

to

     If Target.Value = "No" And Target.Offset(, 16).Value = True Then

 

[FrancisM]

Still nothing.

 

[Fluff]

That works for me, as long as you check the check box before selecting No in col G