VBA - Compare two columns side by side

[CC268]

Hello,

I need a VBA code that will simply do a side by side comparison (meaning I want it to check if cell A1=B1, A2=B2, A3=B3, etc). If it finds a difference highlight the cell. I know you can do this with conditional formatting, but I would like to do it via VBA if possible.

Thanks.

 

[Eric W]

Try:

Sub HighlightDifs()
Dim r As Long, MyData As Variant, MyRange As Range

    MyData = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For r = 1 To UBound(MyData)
        If MyData(r, 1) <> MyData(r, 2) Then
            If MyRange Is Nothing Then
                Set MyRange = Union(Cells(r, "A"), Cells(r, "B"))
            Else
                Set MyRange = Union(MyRange, Cells(r, "A"), Cells(r, "B"))
            End If
        End If
    Next r
    If Not MyRange Is Nothing Then MyRange.Interior.Color = vbGreen
    
End Sub

 

[CC268]

Try:

Sub HighlightDifs()
Dim r As Long, MyData As Variant, MyRange As Range

    MyData = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For r = 1 To UBound(MyData)
        If MyData(r, 1) <> MyData(r, 2) Then
            If MyRange Is Nothing Then
                Set MyRange = Union(Cells(r, "A"), Cells(r, "B"))
            Else
                Set MyRange = Union(MyRange, Cells(r, "A"), Cells(r, "B"))
            End If
        End If
    Next r
    If Not MyRange Is Nothing Then MyRange.Interior.Color = vbGreen
    
End Sub

Thanks that works nicely! How can I adjust it so if I want to compare column A and D it will work? I tried replacing B with D, but it highlighted everything. Also if I want to start from Row 2 and not Row 1 how would I adjust that?

 

[Eric W]

I can actually write this shorter which might be easier to understand. Just change the As and Ds to the columns you want, and the 1 on the For line to the starting row:

Sub HighlightDifs2()
Dim r As Long

    For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row     ' From row 1 to the last row with data
        If Cells(r, "A") <> Cells(r, "D") Then
            Cells(r, "A").Interior.Color = vbRed
            Cells(r, "D").Interior.Color = vbRed
        End If
    Next r
    
End Sub

The original code I wrote for efficiency - it only reads the sheet once, and writes the sheet once. It can be updated to do the same thing:

Sub HighlightDifs()
Dim r As Long, MyData As Variant, MyRange As Range

    MyData = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For r = 1 To UBound(MyData)
        If MyData(r, 1) <> MyData(r, 4) Then
            If MyRange Is Nothing Then
                Set MyRange = Union(Cells(r, "A"), Cells(r, "D"))
            Else
                Set MyRange = Union(MyRange, Cells(r, "A"), Cells(r, "D"))
            End If
        End If
    Next r
    If Not MyRange Is Nothing Then MyRange.Interior.Color = vbGreen
    
End Sub

The line in red reads the entire range in one line, but it will include columns B and C as well. That's why 2 lines below it checks for column 1 and 4. Plus you need to change the rest of the As and Ds as well.

Hope this helps!

 

[CC268]

Awesome thanks!

 

[CC268]

I can actually write this shorter which might be easier to understand. Just change the As and Ds to the columns you want, and the 1 on the For line to the starting row:

Sub HighlightDifs2()
Dim r As Long

    For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row     ' From row 1 to the last row with data
        If Cells(r, "A") <> Cells(r, "D") Then
            Cells(r, "A").Interior.Color = vbRed
            Cells(r, "D").Interior.Color = vbRed
        End If
    Next r
    
End Sub

The original code I wrote for efficiency - it only reads the sheet once, and writes the sheet once. It can be updated to do the same thing:

Sub HighlightDifs()
Dim r As Long, MyData As Variant, MyRange As Range

    MyData = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For r = 1 To UBound(MyData)
        If MyData(r, 1) <> MyData(r, 4) Then
            If MyRange Is Nothing Then
                Set MyRange = Union(Cells(r, "A"), Cells(r, "D"))
            Else
                Set MyRange = Union(MyRange, Cells(r, "A"), Cells(r, "D"))
            End If
        End If
    Next r
    If Not MyRange Is Nothing Then MyRange.Interior.Color = vbGreen
    
End Sub

The line in red reads the entire range in one line, but it will include columns B and C as well. That's why 2 lines below it checks for column 1 and 4. Plus you need to change the rest of the As and Ds as well.

Hope this helps!

So using this code this morning I realized that the following line only checks for last row in Column A. What happens if Column D is one or two rows longer? Then it won't capture the entire range correct? Is there a way to make it capture the last row regardless of whether A or D is longer?

[COLOR=#FF0000] MyData = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).Value[/COLOR]

 

[Eric W]

Sure. There are other ways, depending on how your sheet is laid out, but this should work:

Sub HighlightDifs()
Dim r As Long, MyData As Variant, MyRange As Range, LastRow As Long

    LastRow = WorksheetFunction.Max(Cells(Rows.Count, "A").End(xlUp).Row, _
                                    Cells(Rows.Count, "D").End(xlUp).Row)
                                    
    MyData = Range("A1:D" & LastRow).Value
    For r = 1 To UBound(MyData)
        If MyData(r, 1) <> MyData(r, 4) Then
            If MyRange Is Nothing Then
                Set MyRange = Union(Cells(r, "A"), Cells(r, "D"))
            Else
                Set MyRange = Union(MyRange, Cells(r, "A"), Cells(r, "D"))
            End If
        End If
    Next r
    If Not MyRange Is Nothing Then MyRange.Interior.Color = vbGreen
    
End Sub

The same idea will work on the other macro too.