Looping through cell & generate Msg Box

[FrancisM]

I am trying to I am trying to write a macro to loop through a column (Z), & find the cells with a "Yes" in them. When a Yes is found I am trying to generate the following Msg “"Check to verify veteran data is entered in FY ## REFERALS. "It's critical that the veteran data is captured." & “You have entered No into cell (cell name).
Here is some background:
Cell Range F4:F10, F13:F17 (Yes/No) is linked to Cell Range X4:X10, X13:X17, & has the following formula in Column X (=F4="no"). If No is found TRUE is return value.
Cell Range I4:I10, I13:I17 has a Check box & is linked to Cell Range W4:W10, W13:W17 (Boolean answer).
Cell Range Y4:Y10, Y13:Y17 has this formula =AND(W4=TRUE, X4=TRUE).
Cell Range Z4:Z10, Z13:Z17 has this formula =IF(Y4, "Yes", "No")
Here is my current code:

Private Sub Worksheet_Change(ByVal target As Range)
im rng As Range, cell As Range
    Set rng = Range("Z:Z")
     For Each cell In rng
Next cell
    cell.Value = cell.Value
       'The code below is a reminder to enter data in the Referral Workbook.
     If Intersect(target, ActiveSheet.Range("Z:Z")) Is Nothing Then Exit Sub
 
     If target.Value <> "Yes" Then Exit Sub
 
     If ReferralsCalled = False Then
 
     'Shows a 3 line message box.
 
          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, "Vocational Services Database"
 
         Call Referals
End If
End Sub

Currently it hangs on: If ReferralsCalled = False Then
I don’t know if my code is correct. I know that target.Address is not correct. I used it elsewhere & have not fixed it yet. Please inform me what I am doing wrong.
Any help is appreciated.

 

[Fluff]

Rather than using the calculate event to trigger a change event, why not just use the calculate on its own?
& check each cell in Col Z for a yes?

 

[FrancisM]

Column Z has a formula. =IF(Y4, "Yes", "No"). I thought that you couldn't have a formula in a cell.

 

[Fluff]

I thought that you couldn't have a formula in a cell.

Me thinks that would make Excel pretty useless as a spreadsheet
I assume that you are referring to a change event, in which case you are right in that a change event won't trigger based on a formula, but a calculation event will.
So rather than using the calc event to copy paste data, which in turn triggers the change event. Just use the calc event on it's own by looping through the cells you're interested into check if they say "Yes"

 

[FrancisM]

Yep. I just use it, because of my boss. Ok. I will start working on your suggestion, of using the Calculate event by it self. I will probably will not have a chance to get back to you till next week.

 

[FrancisM]

This is my first attempt at looping. Please forgive me for a lot of questions. Column Y has the following formula =AND(W4=TRUE,X=TRUE), this Column is based on Column W & X(both Boolean formulas). Do we actually need Column Z. I had it to convert the answer to "Yes" in Column Z.

 

[Fluff]

Easiest option would be to get rid of the checkboxes & get the user to insert an X (or a Y or N) into the cell & then you can use the Change Event.

 

[FrancisM]

True. But again my boss. Is there a link you could direct me to.
Here is my 1st attempt at looping:

Private Sub Worksheet_Calculate()
If Range("X4:X17").Value = "True" Then
MsgBox "Hi"
Else
Range("X4:X17").Value = "False"
End If
  End Sub

Am I heading in the right direction?

 

[Fluff]

Have a look at For Each loops
https://trumpexcel.com/vba-loops/#For-Each