vba Help: Not Equal sign "<>" doesn't work. Need substitute. "<>" is really "Less&Greater"

[Xceller]

This seems incredibly simple, but the comparative operator, Not Equal sign "<>" doesn't work because it really reads as "Less and greater". See below:
Column A, I have numeric value from 1 to 10
Column B, I have 1,3,5,7,9

A B
1 1
2 3
3 5
4 7
5 9
6
7
8
9
10


I want to compare column A to Column B and highlight all the unmatched numbers in column A, in this example, it would be 2,4,6,8,10.
The result should be as follows:

A B
1 1
2 3
3 5
4 7
5 9
6
7
8
9
10

and hopefully have the unmatched items listed in column D.

D
2
4
6
8
10

Here's my code:
Sub Unmatched()

For i = 1 To 10
For j = 1 To 5
newList = ""
If Cells(i, "A").Value <> Cells(j, "B").Value Then
Cells(i, "A").Interior.Color = RGB(0, 255, 0)
End If
Next j
Next i

End Sub

 

[JoeMo]

If <> doesn't work we are all in trouble. Try it this way:

Sub Unmatched()
For i = 1 To 10
    For j = 1 To 5
        If Cells(i, "A").Value = Cells(j, "B").Value Then
            GoTo Nx
        End If
    Next j
    Cells(i, "A").Interior.Color = RGB(0, 255, 0)
Nx:
Next i
End Sub

 

[ret44]

I'm not sure if you're actually getting at what you're looking for with your code right now, so made some edits to get it to output in Column D - I also got rid of the J loop, as it doesn't seem necessary for the method that I used. Just used a "match" worksheetfunction to test if the value on the left was in the list on the right. The one weird part about this is that worksheetfunction.match errors if it doesn't find something, so the on error resume next, and on error goto 0 (to resume error handling) are necessary -there is probably a slicker way to do this that I'm not aware of.

Sub Unmatched()

On Error Resume Next 'go to next line of code if an error pops up from a match not being found (your goal)


rc = 0 'to keep track of rows in column D


For i = 1 To 10
newList = ""


If WorksheetFunction.Match(Cells(i, "A").Value, Range("b1:b5"), False) = False Then ' will not error on "True" evaluations, so will not output a value in column d
    Cells(rc + 1, "D").Value = Cells(i, "A").Value
    rc = rc + 1 'increment rowcount in column D
End If


Next i


On Error GoTo 0 'turns error handling back on


End Sub

This appeared to work for me

 

[Xceller]

Hi JoeMo,

Thanks for the quick help. It works great. Could you kindly explain your code, especially," Goto Nx ........Nx: " How does this work?

Thanks,

 

[Xceller]

ret44 - Thank you for your help. Your code works great. Just for the purpose of learning, I am curious of how JoeMo's code work, "Goto nx" "....Nx:"

 

[ret44]

The Goto "something" / "something": is often an error handling convention, but can be used to jump around in code.

Essentially - there's the logic test to see if your value is in the second column. If that returns "True", then "Goto Nx" get executed, which skips to "Nx:" and executes that code . You'll notice that "Nx:" comes after the part where the cell gets recolored if it doesn't match. If that logic test returns false, then it goes back to the next value of j to see if that matches - once it gets through all of them, and there is no match, it'll output the colored cell. If any had matched, it would have jumped out of the "J" loop (to Nx: ) to the next round of the "i" loop, as that value clearly had a match

 

[Xceller]

ret44 - Thank you for the explanation. I think I got the logic now.

 

[Jonmo1]

Glad you got resolution..
But it should be noted that the problem is NOT with the <> logic.

The <> is doing exactly what you think it does.
It's the logic of the i and j loops that is NOT doing what you think it's doing.

I would recommend taking your original code
Turn on the Locals Window in VBA (View - Locals)
And step through the code using F8
You can observe the values of i and j as they incriment through the loops, and watch what the IF statement does using those variables.