Event codes with two conditions

[rhmkrmi]

Hi, Can you please help me get an event code with two conditions to generate a message for two scenarios as follows?

1)
I am trying to translate this formula into VBA:
IF(AND($A1="Home",$B1="School"),"Invalid Combination","")
I want the event code to check these conditions for rows 1 to 20.
I want the message to pop up only and as soon as these conditions are met on any row in range A1 to B20.

2)
I need an event code with a message box for this formula:
IF(AND(ISNUMBER($A1),ISBLANK($Z1)),"Parameter Missing","")
I want the event code to check these conditions for rows 1 to 20.
I want the message to pop up only if these conditions are met and at the time the user leaves the sheet not as soon as the conditions are met.

Thank you.

 

[James006]

Hi,

Event macros are triggered ... for example ... by a cell change ...

Which are the ' source ' cells which will trigger your macro ...?

 

[rhmkrmi]

In the first scenario, the cells that trigger the macro are A1:A20 and B1:B20.

In the second scenario, they are A1:A20 and Z1:Z20.

Thank you.

 

[James006]

Hi again,

You can following macro for your first scenario

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then ExitSub
If Intersect(Target, Range("A1:B20")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case 1
            If Target = "Home" And Target.Offsetset(0, 1) = "School" Then
                MsgBox " Invalid Combination "
            End If
        Case 2
            If Target = "School" And Target.Offsetset(0, -1) = "Home" Then
                MsgBox " Invalid Combination "
            End If
    End Select
End Sub

Hope this will help

 

[rhmkrmi]

Thank you very much.

I have difficulty combining it with the below which is some existing codes on the same sheet. Where and how do I add your code to test it?

Private Sub Worksheet_Change(ByVal Target As Range)


' ***BLOCK1***
Dim sUndoList As String


On Error Resume Next


If Not Intersect(Target, Range("A1:Z100")) Is Nothing Then
sUndoList = CommandBars.FindControl(ID:=128).List(1)
If Left(sUndoList, 5) = "Paste" Or sUndoList = "Auto Fill" Or sUndoList = "Drag and Drop" Then
Application.EnableEvents = False
Application.Undo
Application.OnUndo "", ""
Application.EnableEvents = True
End If
End If



' ***BLOCK2***
Dim rng As Range
Dim cell As Range
Dim rw As Long


' See if any cells updated in column B
Set rng = Intersect(Target, Range("B:B"))
If rng Is Nothing Then Exit Sub


Application.EnableEvents = False


' Loop through updated cells in column B
For Each cell In rng
rw = cell.Row
Select Case cell.Value
Case "Home"
Range(Cells(rw, "F"), Cells(rw, "S")) = "N/A"
Range(Cells(rw, "E"), Cells(rw, "E")) = ""
Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Color = 15132390
Range(Cells(rw, "E"), Cells(rw, "E")).Interior.Pattern = xlNone
Case "School"
Cells(rw, "E") = "N/A"
Range(Cells(rw, "F"), Cells(rw, "S")) = ""
Cells(rw, "E").Interior.Color = 15132390
Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Pattern = xlNone
Case Else
Range(Cells(rw, "E"), Cells(rw, "S")) = ""
Range(Cells(rw, "E"), Cells(rw, "S")).Interior.Pattern = xlNone
End Select
Next cell

Application.EnableEvents = True


End Sub


Private Sub Worksheet_Activate()
MsgBox "Check your selections."
End Sub

 

[James006]

May be

' Loop through updated cells in column B
    For Each cell In Rng
        rw = cell.Row
        Select Case cell.Value
            Case "Home"
                Range(Cells(rw, "F"), Cells(rw, "S")) = "N/A"
                Range(Cells(rw, "E"), Cells(rw, "E")) = ""
                Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Color = 15132390
                Range(Cells(rw, "E"), Cells(rw, "E")).Interior.Pattern = xlNone
                
            Case "School"
                Cells(rw, "E") = "N/A"
                Range(Cells(rw, "F"), Cells(rw, "S")) = ""
                Cells(rw, "E").Interior.Color = 15132390
                Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Pattern = xlNone
                ' Added Test ''''''''''''''''''''''''''''''''''''''''''''''''''
                If cell.Offset(0, -1) = "Home" Then MsgBox "Invalid Combination"
            Case Else
                Range(Cells(rw, "E"), Cells(rw, "S")) = ""
                Range(Cells(rw, "E"), Cells(rw, "S")).Interior.Pattern = xlNone
        End Select
    Next cell

Hope this will help