Ask a question »

Comparison between two excel sheets

November 2015

Comparison between two excel sheets


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.


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.


Thanks to venkat1926 for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:

See also

In the same category

Published by aakai1056.
This document entitled « Comparison between two excel sheets » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.