Ask a question »

Comparison between two excel sheets

May 2015


Comparison between two excel sheets




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.
For unlimited offline reading, you can download this article for free in PDF format:
Comparison-between-two-excel-sheets.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Comparison between two excel sheets » from Kioskea (en.kioskea.net) 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.