Msgbox when add certain text in one column only if a certain value exists in another column

[KC E]

Hello,

Thank you for your help.

I'm using Excel 2016 in Office 365 and have been trying to work on code that will trigger a msgbox when you add the word "Complete" to column O, but only if the word "1x1" is in column H.

I want to try learn more of VBA, and I wrote this code below based on a variation of a code found on the web but nothing happens. No errors and no msgbox, nothing.

I've tried variations of different codes found on the web, and the only ones that work are if I just add "Complete" in column O. If I try to add another criteria, meaning that there must be "1x1" in column H, it won't work. I either get Type Mismatch or nothing happens at all.

I would appreciate the help. Thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

If Not (Application.Intersect(Range("o1:o210"), Target) Is Nothing) Then
    For Each r In Intersect(Target, Range("h10:o210"))
        If r.Value = "1x1" And r.Value = "Complete" Then
            MsgBox "Billing 1x1"
        End If
    Next r
End If
End Sub

 

[vletm]

Hmmm...
When could one cell's value be "1x1" and "Complete" in same time?

If r.Value = "1x1"And r.Value = "Complete"Then
    MsgBox "Billing 1x1"
EndIf

 

[Marc L]

Hi !

Yes vletm, it's a Logic issue !

KC E, try this :​

Private Sub Worksheet_Change(ByVal Target As Range)
             Dim Rg As Range
    If Not Intersect([O1:O210], Target) Is Nothing Then
        For Each Rg In Intersect([O1:O210], Target)
              If Rg.Value = "Complete" And Rg(1, -6).Value = "1x1" Then MsgBox "Row " & Rg.Row & " : billing 1x1"
        Next
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[KC E]

Thank you, both, so much. I am learning VBA and I kept going over and over and didn't even see what I was saying--'r' can't be '1x1' and 'Complete'.

Can you tell me what is the significance of switching the order within the Intersect parenthesis?:

For Each Rg In Intersect(Target, [O1:O210])

to

For Each Rg In Intersect([O1:O210], Target)

Thank you!

 

[Marc L]

Order no matter as you can see within VBA inner help …​

 

[KC E]

OK, thank you. I think I see on the Office Dev Center site that Intersect just says the parameters are Arg1, Arg2, etc. I see, thank you, again.