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!




Reply With Quote
Bookmarks