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.

 

[mse330]

I am not really sure if I understood your requirement exactly but would the below code work for you ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, Rg As Range
Set Rg = ActiveSheet.Range("Z:Z")
    If Not Intersect(Target, Rg) Is Nothing Then
        If LCase(Target.Value) = "yes" Then
        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 " & Replace(Target.Address, "$", ""), vbInformation, "Vocational Services Database"
        End If
    End If
End Sub

 

[FrancisM]

No response or error message. The bottom line, is that I am trying to have the error message pop ug & the other file called when "Yes" is in Column Z. Here is a link to one drive for a sample. I have included your code.

https://1drv.ms/x/s!Ak-4iXjPpsJMgSLMZ7BZUHuzG2Ew?e=o9N6t3

 

[mse330]

Ok, how about replace my previous code with this

Dim x As Long
For x = 4 To 17
    If LCase(Cells(x, "Z").Value) = "yes" Then
        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 " & Replace(target.Address, "$", ""), vbInformation, "Vocational Services Database"
    End If
Next

Just a quick question, are you handling the data in column W by VBA ? It is all FALSE without any formulas

 

[Fluff]

You cannot use a Change event for this, because Col Z is a formula & one of the cells it relies on (col W) is populated via a checkbox.
Change events are not triggered by formulae, or clicking a checkboxe.

 

[FrancisM]

It did fire because as Fluff said change events don't work with formulas & it was based on column W( no formula).

 

[FrancisM]

I had tried VBA to copy in Column Z into AA, & I did copy the values only. I as not successful in getting to run as a calculated change. The 1 question I have, is it possible to have it fire wit 'YES" response in AA.

 

[Fluff]

Change your code to look at col AA instead of Z.

 

[FrancisM]

Ok. Now it is firing if the response Yes or No. However I didn't get this far before.

 

[FrancisM]

Fluff Thanks. However I was able to pinpoint the problem. I have code to copy the values from Column Z. I tested the code with 1 row of values & it worked. When I did standard range of cells it ran through the entire range, even if the value was No.
Here is the working code:

Private Sub Worksheet_Calculate()
'Copies values from ZZ into AA.
 Range("Z4:Z4").copy
  Range("AA4:AA4").PasteSpecial (xlPasteValues)
  
    Application.CutCopyMode = False
        
  End Sub

This is the code I said was causing the problem

Private Sub Worksheet_Calculate()
'Copies values from ZZ into AA.

 Range("Z4:Z10").copy
  Range("AA4:AA10").PasteSpecial (xlPasteValues)
  
    Application.CutCopyMode = False
    
   Range("Z13:Z17").copy

   Range("AA13:AA17").PasteSpecial (xlPasteValues)

   Application.CutCopyMode = False
    
  End Sub

I believe my problem is with my Worksheet_Calculate code, but I can't find it.