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]

What values do you have in Col G/
Is the Value in col W TRUE or "True"
Please explain

I tested you code & got the my message when No, Yes is entered

as it makes no sense to me.
You would not get the Msgbox if col G is blank as it does not contain the word No

 

[Tim_Excel_]

For Each Cell In Range("G4:G10, G13:G" & LastRow) 'For each cell in G4-G10, and G13-G(last row in col G)
    If InStr(1, Cell.Value, "No") <> 0 Then 'If the cell in the current iteration contains "No" then...
        If Cell.Offset(, 16).Value = True Then '...If the value of the cell 16 columns to the right of the current cell is TRUE then...
            MsgBox "You have No & true" '...display the message box
            Exit For 'Exit the loop
        End If
    End If
Next

Study this, it will answer your question

 

[FrancisM]

Tim_Excel In theory I understand I understand what you are saying, However I am really struggling to convert what you were kind enough to provide, to my situation. I am struggling with the if parts of the code. I think I understand IF message parts.

 

[Tim_Excel_]

Well, in order for us to be able to help you, you have to be clear about what it is you are trying to achieve

 

[Fluff]

@FrancisM
Could you answer the my questions in post#11 and explain what you are trying to do.

 

[FrancisM]

Tim_xcel Thank you now I understand what you ere saying however, The message box still comes up with No, Yes, & if the cell is cleared. True in column 16 "W" has no effect. I really appreciate the time you are taking to explain things.

 

[FrancisM]

In Column G there is a Yes/No response, I am only concerned about No. In Column W there is TRUE, not "True". I am trying to get the msgbox to run only if there are 2 TRUE statements. 'No" in Column G & TRUE in Column W.

I can't explain what is happening with Fluffy's code. I am just reporting what is happening. I agree with you, it does not make any sense.

 

[Fluff]

How about

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

 

[Tim_Excel_]

Try the following

     Dim lastRow As Long
     Dim cell As Range

With Workbooks(REFERENCE).Sheets(REFERENCE)
lastRow = .Range("G" & Rows.Count).End(xlUp).Row
For Each Cell In .Range("G4:G10, G13:G" & LastRow) 'For each cell in G4-G10, and G13-G(last row in col G)    
    If InStr(1, LCase(Cell.Value), "no") <> 0 Then 'If the cell in the current iteration contains "No" then...
        If Cell.Offset(, 16).Value = True Then '...If the value of the cell 16 columns to the right of the current cell is TRUE then...
            MsgBox "You have No & true in the same row" '...display the message box
            Exit For 'Exit the loop
        End If
    End If Next Cell
End With

If this does not work still, then something is off in what you are telling us.
In "Cell.Offset(,16).Value = True", True is treated as a boolean and not a string. So, not an issue.

Mind you that this code checks for "No" and TRUE in the same row. If you simply want to check if "No" and TRUE exist in aforementioned columns, use the following

With Workbooks(REFERENCE).Sheets(REFERENCE)
   Set FindNo = .Range("G:G").Find("*No*")
   If Not FindNo is Nothing Then
       Set FindTRUE = .Range("W:W").Find(True)
       If Not FindTRUE is Nothing then MsgBox "Found both No and TRUE"
   End If
End With

 

[FrancisM]

Correct. Column W is a Boolean response. Column G contains "No". Does it matter if t is proper ca or lower?