VBA Code Question - Novice

[hiply]

Hi

I've developed a worksheet with a rating scale. I've been attempting to code where if an exclamation mark occurs, a pop message appears. However, the pop up message only appears when I double click on the cell where the exclamation mark (!) occurs. My desired outcome is when the exclamation mark appears (as a result based on formula) I like the pop message to appear and not when I have double click on the cell. Could someone please help a novice VBA coder.

Thank you

This is the formula that results to the exclamation "!". If exclamation "!" appears within the range, I would like a pop up message to occur as a result.

Formula
=IF(COUNT(W16:AC16)>1,"!",IF(COUNT(W16:AC16)<1,"←",""))

The VBA code below only works when I double click on the cell where the "!" appears and the pop up message appears. Anyway to fix the VBA code where the result of the formula will show the pop message?

VBA Code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Range("K16:K18").Find(what:="!", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True) Is Nothing Then
MsgBox "Message"
End If
End Sub

Thank you

 

[DanteAmor]

Hi

I've developed a worksheet with a rating scale. I've been attempting to code where if an exclamation mark occurs, a pop message appears. However, the pop up message only appears when I double click on the cell where the exclamation mark (!) occurs. My desired outcome is when the exclamation mark appears (as a result based on formula) I like the pop message to appear and not when I have double click on the cell. Could someone please help a novice VBA coder.

Thank you

This is the formula that results to the exclamation "!". If exclamation "!" appears within the range, I would like a pop up message to occur as a result.

Formula
=IF(COUNT(W16:AC16)>1,"!",IF(COUNT(W16:AC16)<1,"←",""))

The VBA code below only works when I double click on the cell where the "!" appears and the pop up message appears. Anyway to fix the VBA code where the result of the formula will show the pop message?

VBA Code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Range("K16:K18").Find(what:="!", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True) Is Nothing Then
MsgBox "Message"
End If
End Sub

Thank you

The Change event is activated only when you modify a cell.
In this case the message should be displayed when you modify any of the cells in the W16:AC16 range, as long as they are values ​​and not formulas.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("W16:AC16")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Not Range("K16:K18").Find(what:="!", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True) Is Nothing Then
            MsgBox "Message"
        End If
    End If
End Sub