excel - How to compare two sheets in different work books and highlight the difference in 2nd sheet? -

sub compare2sheetsex() 'and highlight diffrence     dim wb1 workbook, wb2 workbook, sh1 worksheet, sh2 worksheet     set wb1 = workbooks(inputbox("enter b1"))     set wb2 = workbooks(inputbox("enter b2"))     set sh1 = wb1.sheets(inputbox("enter s1"))     set sh2 = wb2.sheets(inputbox("enter s2"))     rcount = sh1.usedrange.rows.count     ccount = sh1.usedrange.columns.count     dim r long, c integer     r = 1 rcount         c = 1 ccount             if sh1.cells(r, c) <> sh2.cells(r, c)                 sh2.cells(r, c).interior.colorindex = 6             end if         next c     next r     set sh1 = nothing     set sh2 = nothing end sub 

q:i tried compare 2 sheets in different workbooks unable execute code above.

aside undeclared variables (using option explicit prevent this, , typos in variable names), code works fine me minor modifications:

option explicit sub compare2sheetsex() 'and highlight diffrence     dim wb1 workbook, wb2 workbook, sh1 worksheet, sh2 worksheet     dim rcount long, ccount long     set wb1 = workbooks(inputbox("enter b1"))     set wb2 = workbooks(inputbox("enter b2"))     set sh1 = wb1.sheets(inputbox("enter s1"))     set sh2 = wb2.sheets(inputbox("enter s2"))     rcount = sh1.usedrange.rows.count     ccount = sh1.usedrange.columns.count     dim r long, c integer     r = 1 rcount         c = 1 ccount             if sh1.cells(r, c) <> sh2.cells(r, c)                 sh2.cells(r, c).interior.colorindex = 6             end if         next c     next r     set sh1 = nothing     set sh2 = nothing end sub 

screenshot:

the thing notice both workbooks must open code work. if entering filename & path, you'll need use workbooks.open method input boxes, e.g.:

set wb1 = workbooks.open(inputbox("enter b1")) set wb2 = workbooks.open(inputbox("enter b2")) 

otherwise, don't have error-handling inputboxes it's possible if receive subscript out of range errors, did not correctly input workbook or worksheet names inputboxes.