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]

Not with the code in post#18

 

[FrancisM]

Dumb question. workbooks (Reference) refers to the name of the workbook, Sheets (Reference) referrers to the sheets that have the cells in question. If workbooks (Reference refers to the name of the workbook, can I use active workbook?

 

[Fluff]

You do not need to worry about workbooks, do you want the code to work on one particular sheet, or all sheets in the workbook?
Also you have made no mention if the last code I supplied worked or not.

 

[FrancisM]

Sorry I did not get back sooner, right after I replied, left my office. I tried the second response & got this error message. Run-time error '9' subscript out of range on this line of code. "With Workbooks(REFERENCE).Sheets(REFERENCE)" .

 

[FrancisM]

I get a syntax error on End If Next Cell Using the first batch of code.

 

[Fluff]

What happens with this

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:G" & lastRow)
      If LCase(cell.Value) = "no" Then
         If cell.Offset(, 16).Value = True Then
            MsgBox "You have No & true"
         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

Also do you want this to work on every sheet in the workbook, just one, or some of them?

 

[FrancisM]

I am so sorry. You did ask that question & I failed to answer it. there are certain sheets do not require he code to run. I will try the newest code.

 

[FrancisM]

No change. It still does the same thing as past attempts. Just a reminder. No will be entered one time & TRUE will at another time. The name is entered as a new entry & True appears when a box is checked & the cell in W that was FALSE, is now TRUE.

 

[Fluff]

Are they both in the same row?

 

[FrancisM]

yes.