1 Attachment(s)
Conditional Appearance of msgbox
Case:
There are 3 cells- "P20", "U20" and "Z20".
"Z20" contains the formula =IF(P20<U20,"??","")
I've this VBA in the sheet
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range, Rg As Range
On Error Resume Next
Set Rg = Application.Intersect(Target, Range("Z20"))
If Not Rg Is Nothing Then
For Each xCell In Rg
If xCell.Value = "??" Then
MsgBox "Entry Error"
Exit Sub
End If
Next
End If
End Sub
Private Sub Worksheet_selectionChange(ByVal Target As Range)
Dim xCell As Range, Rg As Range
On Error Resume Next
Set Rg = Application.Intersect(Target, Range("Z20"))
If Not Rg Is Nothing Then
For Each xCell In Rg
If xCell.Value = "??" Then
MsgBox "Entry Error"
Exit Sub
End If
Next
End If
End Sub
Problem:
Msgbox appears only when I select the Cell "Z20" (If it contains "??")
My requirement:
1. Msgbox should appear immidiately after appearance of "??" in the cell "Z20"
That is, whenever a value is entered in the cell "P20" or "U20", and P20<U20 (as the formula set in cell Z20), immidiately the msgbox should appear.
2. Also, the msgbox should appear when the cell Z20 contains "??" and the cell Z20 is selected. (It is working currently)
Please find the attachment below for more clarity.
Many Thanks!
