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?

 

[Fluff]

And your getting the msgbox saying "You have No & true" appearing when the row does not have "No" & true on the same row?

 

[FrancisM]

When No & TRUE is not on the same line. Yes.

 

[Fluff]

Ok, select the entire row where it's giving the wrong result & then run this

Sub Chk()
   Debug.Print "|" & join(Application.Index(Selection, 1, Array(7, 23)), "|") & "|"
End Sub

Below the code window there should be the Immediate window (Ctrl G will bring it up if it's not visible)copy and paste whatever is in the immediate window to the thread

 

[FrancisM]

When I put ' in front of my msgbox the is no response from the code. If I remove it, it appears that it is bypassing the code & going straight to my message.

 

[Fluff]

In the code I supplied in post#26 I had deliberately commented out your message box, in order to avoid any possible misunderstanding. Also in post#31 I asked

And your getting the msgbox saying "You have No & true" appearing when the row does not have "No" & true on the same row?

If you make changes to code supplied and/or do not accurately reply to question, it is very difficult to help.
Please run the code again, dose the msgbox saying "You have No & true" appear & if so is it on the correct row?

 

[FrancisM]

If I gave you a link to a drop box would that help? If it would I will do that after work.

 

[Fluff]

Yup that's fine, just post the link to the thread.

 

[FrancisM]

here is the link.

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

 

[Fluff]

Thanks for the file, but which sheets is the code meant to work on?
Some of the sheets have a Yes/No dropdown in col F rather than G, but col W is blank

 

[FrancisM]

Interesting! Thanks for letting me know. I plan on deleting all non report/chart sheets, except October_Meetin_#_1. I checked that sheet & column G does have yes/no, & Column W does have a Boolean answer TRUE/FALSE. That is related to column H, a check box. When the box is checked it changes to TRUE. Again thanks for finding those errors.