CODEWITHSUNDEEP
vbaexcel
Alexander Bradley
Alexander Bradley

Reputation: 23

How do I compare two different columns on two different worksheets and highlight differences?

I want to be able to compare two worksheets, and highlight the cells on ws2 if there are differences in dates in columns H on ws1 and column F on ws2. The trouble I have is that they are two different columns starting at two different cells (comparing H9+ on ws1 to F10+ on ws2) This gives me no errors but nothing seems to happen. Here is what I have so far:

Sub matchMe()
Dim wS As Worksheet, wT As Worksheet
Dim r1 As Range, r2 As Range
Dim cel1 As Range, cel2 As Range

Set wS = ThisWorkbook.Worksheets("Project Status Report L3")
Set wT = ThisWorkbook.Worksheets("Demand Mapping - Active")

With wS
    Set r1 = .Range("H9", .Cells(.Rows.Count, .Columns("R:R").Column).End(xlUp))
End With

With wT
    Set r2 = .Range("F10", .Cells(.Rows.Count, .Columns("G:G").Column).End(xlUp))
End With

On Error Resume Next
For Each cel1 In r1
    With Application
        Set cel2 = .Index(r2, .Match(cel1.Value, r2, 0)) 'find match in sheet2
        If Err = 0 Then
            If cel1.Offset(, 8) <> cel2.Offset(, 8) Then cel2.Interior.ColorIndex = 1  'if difference, color
        End If
        Err.Clear
    End With
Next cel1

End Sub

Upvotes: 2

Views: 297

Answers (2)

Ashwith Ullal
Ashwith Ullal

Reputation: 263

Sub comparison()
For i = 2 To 1000
For j = 2 To 1000
If Worksheets(Worksheet).Range("A" & i).Value = Worksheets(Worksheet).Range("L" & j).Value Then
Worksheets(worksheet).Range("N" & j).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

End If
Next j
Next i
End Sub

Upvotes: 0

User30923
User30923

Reputation: 62

I found this code online a while back, it should do what you need. Simply set shtBefore and shtAfter as your worksheet names.

Sub compareSheets(shtBefore As String, shtAfter As String)

Dim mycell As Range
Dim mydiffs As Integer

For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then

        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1

    End If
Next

MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtAfter).Select

End Sub

Upvotes: 1

Related Questions