Comparison between two excel sheets

March 2017




Issue


I have two excel sheet with same value which i need to compare every day .with in same excel sheet I can compare easily using conditional formatting but in case of comparison between two excel sheet conditional formatting is not working so I just want to know VBA code which compare the value sheet 1 a1 and with sheet2 a1 and if it's doesn't match then the value which is in sheet 1 a1 should show in red color.

Solution


I have given a macro below

when do you want this macro to be invoked( under what event ) or do you want to invoke the macro periodically

The macro is:
Sub test()
Dim r As Range, c As Range, j As Integer, k As Integer
With Worksheets("sheet1")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
r.Cells.Interior.ColorIndex = xlNone
For Each c In r
j = c.Row
k = c.Column
If c = Worksheets("sheet2").Cells(j, k) Then
c.Interior.ColorIndex = 3
End If
Next c
End With
End Sub



Please remember that only if A1 or any other cells in column A of sheet 1 is same as the entry in the same address in shseet2 that cell in sheet 1 will be red. Suppose the same entry in any cell in sheet1 is found in some other address in sheet 2 the original cell in sheet 1 will not be red.

Note


Thanks to venkat1926 for this tip on the forum.

Related


Published by aakai1056.
This document, titled "Comparison between two excel sheets," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).