Pop up message to be displayed

[friendlydue2000]

Hi

I have a problem in getting a Pop up message in excel. I dont know whether formula can do the trick or should i take a help of micro.Kindly advise which is preferable and the solution for the same.

The problem is :-

I have 2 conditions and if any of the condition comes true , i want a pop up stating that "you have to calculate"( It should serve like a reminder).

Condition 1: In cell B if i insert "CA", and in cell E if i insert "no" and in cell G if i insert "lease".. I want a pop up stating "you have to calculate"

Condition 2: In cell B if i insert "TX" and in cell H if i insert "yes".. I should get a pop up "you have to calculate"

Awaiting a reply.

Thanks

 

[friendlydue2000]

wow.. amazing.. but i have just forgot or missed one point.. tat is in condition 1

In cell B or c if i insert "CA", and in cell E if i insert "no" and in cell G if i insert "lease".. I want a pop up stating "you have to calculate"

while condition 2 is the same as given before.

Awaiting reply

 

[friendlydue2000]

wow.. amazing.. but i have just forgot or missed one point.. tat is in condition 1

In cell B or c if i insert "CA", and in cell E if i insert "no" and in cell G if i insert "lease".. I want a pop up stating "you have to calculate"

while condition 2 is the same as given before.


Kindly help.. and sorry for the inconvienience

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    
    If IsEmpty(Target) Then Exit Sub
    
    If Target.Count = 1 And Not Intersect(Target, Range("B:C, E:E, G:H")) Is Nothing Then
    
        r = Target.Row
        
        If ((UCase(Range("B" & r)) = "CA" Or UCase(Range("C" & r)) = "CA") And UCase(Range("E" & r)) = "NO" And UCase(Range("G" & r)) = "LEASE") Or _
           (UCase(Range("B" & r)) = "TX" And UCase(Range("H" & r)) = "YES") Then
           
                MsgBox "You have to calaculate.", vbCritical, "Alert!"
                
        End If
    End If
    
End Sub

 

[friendlydue2000]

Hi thanks for your efforts. I indeed need further assistance on it.

Now, when i enter in "B1" coloumn "TX" and in "H1" colomn "YES", and when i click enter,i immediately i need a pop up.

Kindly help me in this..

Thanks

 

[AlphaFrog]

It already does that...

(UCase(Range("[COLOR="Red"]B[/COLOR]" & r)) [COLOR="Red"]= "TX"[/COLOR] And UCase(Range("[COLOR="Red"]H[/COLOR]" & r)) [COLOR="Red"]= "YES"[/COLOR]) Then

 

[mikerickson]

Using DataValidation you can set Validation on Cell B1 the formula

=OR($B1<>"TX",$H1<>"YES")

and copy the validation to the other cells needed.

 

[zugfrig]

Right-click on the sheet tab that you want this to work on and select "View Code" from the pop-up menu. Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    
    If Target.Count = 1 And Not Intersect(Target, Range("B:B, E:E, G:H")) Is Nothing Then
    
        r = Target.Row
        
        If (UCase(Range("B" & r)) = "CA" And UCase(Range("E" & r)) = "NO" And UCase(Range("G" & r)) = "LEASE") Or _
           (UCase(Range("B" & r)) = "TX" And UCase(Range("H" & r)) = "YES") Then
           
                MsgBox "You have to calculate.", vbCritical, "Alert!"
                
        End If
    End If
End Sub

hi can i just apply this to one colum??


eg id any cell in colum B at any stage says "HOLD" the the pop up appear?