Trying to compare cells in column a against cells in column b vba

Question

I'm pretty new to VBA, and have been pretty successful in finding the answers I need, until now. I want to take one value in column A, and see if it appears in column B and perform an action when it does find the value and then go the next column in column B. I feel like I am close just not getting something right.

Here's what I've tried so far

Sub Macro1()
'
' Macro1 Macro            
    Dim currentA As String
    Dim currentB As String
    Dim a As Integer
    Dim b As Integer
    a = 2
    b = 1

    Do Until IsEmpty(ActiveCell)
        Cells(a, b).Select
        currentA = ActiveCell
        Debug.Print (currentA)
        a = a + 1

        Range("b2").Select            
        Do Until IsEmpty(ActiveCell)                
            currentB = ActiveCell                
            If currentA = currentB Then
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .Color = 65535
                    .PatternTintAndShade = 0
                    .TintAndShade = 0
                End With
            End If

            Debug.Print (currentA)                   
            ActiveCell.Offset(1, 0).Select
        Loop                                       
    Loop
End Sub

Can you describe what is not working? What does it do, and what should it do? — Rich Holton, Apr 21 '17 at 13:07
@RichHolton said, could you please describe what's not working? — Drew Szurko, Apr 21 '17 at 13:28

score 1 · Answer 1 · edited Apr 21 '17 at 16:53

Sub CompareCells()
Dim CellInColA As Range
Dim CellInColB As Range
  For Each CellInColA In Application.Intersect(ActiveSheet.UsedRange, Columns("A").Cells)
    For Each CellInColB In Application.Intersect(ActiveSheet.UsedRange, Columns("B").Cells)
      If CellInColB = CellInColA Then
        'found it - do whatever
        CellInColB.Interior.ColorIndex = 3
        Exit For
      End If
    Next CellInColB
  Next CellInColA
End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:25

Here is a possible solution of your problem, using as much from your code as possible:

Option Explicit

Sub TestMe()

    Dim currentA    As String
    Dim currentB    As String

    Dim a           As Long
    Dim b           As Long

    Dim cellA       As Range
    Dim cellB       As Range

    a = 2
    b = 1

    With ActiveSheet
        Set cellA = .Range("A2")

        Do Until IsEmpty(cellA)
            Set cellA = .Cells(a, b)
            a = a + 1

            Set cellB = .Range("B2")

            Do Until IsEmpty(cellB)
                If cellA.Value = cellB.Value Then
                    PaintMe cellA
                    PaintMe cellB
                End If

                Set cellB = cellB.Offset(1, 0)
            Loop
        Loop

    End With
End Sub

Public Sub PaintMe(cellA As Range)

    With cellA.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .Color = 65535
        .PatternTintAndShade = 0
        .TintAndShade = 0
    End With

End Sub

What I have done:

The cells and the ranges are refered to the Activesheet with a dot.
I have updated the loops, so they look better.
I have made a special sub PaintMe that paints both the left and the right column.
I have avoided using ActiveCell, because it is slow and difficult - see more here - How to avoid using Select in Excel VBA macros

That's a sample of the output:

In general, a solution like this is quite not professional, because it has an algorithm complexity of n², which is probably the worst case for this kind of problem. You have 2 loops within each other, and thats the slowest possible solution. There are much better ways to do it, in general. But for excel, it should work.

Trying to compare cells in column a against cells in column b vba

2 Answers2