VBA Code for Pop-Up Window With Specific Word

[Justinian]

I have a worksheet where I want a message box to appear when the letter "A" appears in the range I37:I69. I used to following code to accomplish this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Range("I37:I69").Find(what:="AA", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True) Is Nothing Then
MsgBox "Exact dimensions needed for ceramic pipe due to required shop fabrication. This can affect both pipe costs and leadtime."
End If
End Sub

The problem is that the message box appear when ANY cell is changed, not just when the letter "A" appears in I37:I69.

 

[Justinian]

Sorry, I meant when "AA" appears in the range I37:I6. IF AA is the result of a formula, the message box does not appear; if you manually type in AA, then the message box appears and I cannot figure out why that isd.

 

[DanteAmor]

This event is used when you modify the cell.
Private Sub Worksheet_Change
In your case you are not modifying the cell, since the formula remains the same, the change is the result of the formula.

You can use the calculate event:

Private Sub Worksheet_Calculate()
    Set b = Range("I37:I69").Find(what:="AA", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
    If Not b Is Nothing Then
        MsgBox "Exact dimensions needed for ceramic pipe due to required shop fabrication. This can affect both pipe costs and leadtime."
    End If
End Sub

Or you can use the change event, but monitoring the cells involved in the formula of range I37:I69

 

[Justinian]

Awesome! Thank you!!

 

[DanteAmor]

Youre welcome