VBA - Compare two sheets and highlight changes

0
votes

I'm trying to compare two sheets. The structure of sheet is absolutely the same -> Cell AD4 in Sheet1 should be same as AD4 in Sheet2, if not, then highlight the AD4 cell. It should be done as far as data exists.

The below code does not work, but it does not show any error message.

Sub CompareAndHighlightDifferences()
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, a As Range
Set w1 = Sheets("2019 Project Detail")
Set w2 = Sheets("2019 Project Detail SOURCE")
With w1
  For Each c In .Range("AD4", .Range("AD" & Rows.Count).End(xlUp))
    Set a = w2.Columns(30).Find(c.Value, LookAt:=xlWhole)
    If Not a Is Nothing Then
      If .Cells(c.Row, c.Column).Value <> w2.Cells(a.Row, a.Column) Then
         .Cells(c.Row, c.Column).Font.Color = vbRed
      End If
    End If
  Next c
End With
End Sub

Could I ask you for any advices, please?

Many thanks!

---------------------EDIT----------------------

Sub CompareAndHighlightDifferences()
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range
Set w1 = Sheets("2019 Project Detail")
Set w2 = Sheets("2019 Project Detail SOURCE")

  For Each c In w1.Range("AD4", w1.Range("AD" & Rows.Count).End(xlUp))
    If w1.Cells(c.Row, c.Column).Value = w2.Cells(c.Row, c.Column).Value Then
       w1.Cells(c.Row, c.Column).Interior.Color = vbRed
    End If
  Next c

End Sub
2
excelvba
You might consider using .CurrentRegion in order to get the cell, belonging to the same region. - Dominique
Is there a reason you cannot use conditional formatting? (=A1<>'2019 Project Detail SOURCE'!A1) - cybernetic.nomad
Your <> statement will always be false since you have already determined that the two are equal. - Darrell H

2 Answers

1
votes

The problem is that you're searching value in the other sheet, so even if it's not in the same cell address, it will match.

You can remove this and the following if condition:

Set a = w2.Columns(30).Find(c.Value, LookAt:=xlWhole)

And use the same address when comparing values:

If .Cells(c.Row, c.Column).Value <> w2.Cells(c.Row, c.Column).Value Then

/e: Also, you might want to use interior rather than font color because if the cell is blank, there will be a difference that you won't be able to see

1
votes

I would use something like this:

Sub CompareAndHighlightDifferences()

Dim w1 As Worksheet, w2 As Worksheet

Set w1 = Sheets("2019 Project Detail")
Set w2 = Sheets("2019 Project Detail SOURCE")

With w1
    For Each cel In .UsedRange
        If cel.Value <> w2.Cells(cel.Row, cel.Column).Value Then cel.Font.Color = vbRed
    Next cel
End With

End Sub

Edit: If your sheet is protected, you should add w1.Unprotect in the beginning and w1.Protect at the end.