This code works well WITHOUT an "Or" statement added to it, but doesn't WITH the "Or" statement

[VBAProIWish]

Hello All,

There are 2 Code snippets below. The first code snippet works fine, but the second code snippet with the additional code highlighted in red does NOT. Does anyone know how to fix this? Thanks



FIRST CODE SNIPPET

Sub Forum_Question()
                    
 
   Dim LR As Long
   Dim lc As Integer
   Dim colCN As Integer
   Dim colTF As Integer
   Dim colPA As Integer
   Dim i As Integer
 
   'locate columns containing headers
   With ActiveSheet
      LR = .Cells(.Rows.Count, 1).End(xlUp).Row
      lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
      For i = 1 To lc
         If UCase(.Cells(1, i)) = "CUSTOMER NUMBER" Then colCN = i
         If UCase(.Cells(1, i)) = "NAME" Then colTF = i
         If UCase(.Cells(1, i)) = "REGION" Then colPA = i
      Next i
      'Process the active sheet
      For i = 2 To LR
         If (UCase(.Cells(i, colTF)) = "MARY") _
 _
             And _
 _
             ((.Cells(i, colPA) <> "East")) _
 _
      Then
             .Cells(i, colPA).Interior.ColorIndex = 3
         End If
      Next i
   End With

End Sub

SECOND CODE SNIPPET

Sub Forum_Question()
                    
   Dim LR As Long
   Dim lc As Integer
   Dim colCN As Integer
   Dim colTF As Integer
   Dim colPA As Integer
   Dim i As Integer
 
   'locate columns containing headers
   With ActiveSheet
      LR = .Cells(.Rows.Count, 1).End(xlUp).Row
      lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
      For i = 1 To lc
         If UCase(.Cells(1, i)) = "CUSTOMER NUMBER" Then colCN = i
         If UCase(.Cells(1, i)) = "NAME" Then colTF = i
         If UCase(.Cells(1, i)) = "REGION" Then colPA = i
      Next i
      'Process the active sheet
      For i = 2 To LR
         If (UCase(.Cells(i, colTF)) = "MARY") _
         And _
 _
             ((.Cells(i, colPA) <> "East")) _
            Or _
            ((.Cells(i, colPA) <> "South")) _
 _
    Then _
            
             .Cells(i, colPA).Interior.ColorIndex = 3
         End If
      Next i
   End With
End Sub

 

[Andrew Poulsom]

I think you need And rather than Or.

 

[gsistek]

I would remove some parantheses from

            ((.Cells(i, colPA) <> "East")) _
            Or _
            ((.Cells(i, colPA) <> "South")) _

to

            ((.Cells(i, colPA) <> "East") _
            Or _
            (.Cells(i, colPA) <> "South")) _

to make sure the Or is evaluated before the And.

 

[VBAProIWish]

Ok, sorry for the delay in a response guys.

Here's the latest...and after much and much trial and error...

Andrew - It turns out your response was the right one, thank you!
gsistek - I tried a very large amount of variations with open/close parens and could not get it to work. It looks like the "And" statement is what the issue was, but thank you anyway.

Just a Side Note- While using the "And" instead of "Or" was correct to me, I still don't understand logically, how that makes sense. When I think "And", I'm thinking that ALL of the conditions have to be met in order for a cell to be highlighted red. It just makes no sense to me, but...it worked and that's the important thing!

Thanks for all the help to both!

 

[Andrew Poulsom]

Let's say you have three names a, b and c and you want to process only c. If you say:

If name <> "a" Or name <> "b" Then

If name is a then it's not b so Or will return True. If name is b then it's not a so Or will return True. If name is c then it's not a or b so Or will return True. In that case all names will be processed.

But if you say:

If name <> "a" And name <> "b" Then

If name is a then it's not b so And will return False. If name is b then it's not a so And will return False. If name is c then it's not a or b so And will return True. In that case only c will be processed.

 

[VBAProIWish]

Well, that's why you are an MVP!

I think I was thinking along the lines of the English language. Fox example, if someone says "do you want 1 apple OR 1 orange OR 1 banana", I will only get my choice of ONE of them, not all of them...I'm viewing "OR" like "either".

Whereas, if someone says "do you want 1 apple AND 1 orange AND banana?" I can have ALL of them.

I know I shouldn't think that way with VBA, so I will try not to.

Thanks much for the clarification Andrew!

 

[Andrew Poulsom]

Your thinking is right, but you are forgetting about the inequality test. That changes things.

 

[Jonmo1]

I think I was thinking along the lines of the English language. Fox example, if someone says "do you want 1 apple OR 1 orange OR 1 banana", I will only get my choice of ONE of them, not all of them...I'm viewing "OR" like "either".

For that to be an accurate analogy to your initial problem, you would have to rephrase that sentence to
"do you NOT want 1 apple OR 1 orange OR 1 banana"

In which case, if you recieve an apple, then you did NOT recieve a banana or an orange
If you recieve a banana, then you did NOT recieve the apple or an orange.
If you recieve an Orange, then you did NOT recieve an apple or a banana.

 

[VBAProIWish]

Ok, that might make a bit more sense now.

Thanks to both