Comparing Two Worksheets

[RFJ]

I've got two worksheets of identical layout each comprising multiple tables
of numerical information.

Is there a way I can automatically highlight cells in one worksheet where
the corresponding value in the other worksheet is different.

TIA

RobJ

 

[Chip Pearson]

There is no way to do this automatically. You need to use some
VBA code to do it. For example,

Sub AAA()
Dim RngAddr As String
Dim Rng As Range
Dim Rng2 As Range

For Each Rng In Worksheets("Sheet1").UsedRange.Cells
Set Rng2 = Worksheets("Sheet2").Range(Rng.Address)
If Rng <> Rng2 Then
Rng2.Interior.ColorIndex = 5
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Max]

One way could be via conditional formatting (CF), but we need to mirror the
tables over on one of the 2 sheets, as CF doesn't allow cross references to
other sheets (at least in xl97, that is)

Assume a table in Sheet1's A1:C3 is to be compared with the identically
placed one in Sheet2's A1:C3, with the highlighting done in Sheet2's table

In Sheet2
-----
Put in say, A5: =IF(Sheet1!A1="","",Sheet1!A1)
Copy A5 across and down to C7 to mirror Sheet1's table

Then select A1:C3 (with A1 active),
Click Format > Conditional Formatting
Under Condition 1, make the settings:
Formula is| =A1<>A5
Click Format button > Patterns tab > Yellow ? > OK
Click OK at the main dialog

 

[Dave Peterson]

One more way if you're not using format|conditional formatting.

Select your range on sheet1 (I used A1:X99).

With A1 the activecell
format|Conditional formatting
formula is: =A1<>INDIRECT("'sheet2'!rc",0)

give it a nice format

 

[Max]

Splendid, Dave ! Thanks.
Direct, simple way to do it using CF.

 

[RFJ]

And, with a minor amendment it works across different spreadsheets - my
ultimate goal <BG>. Thanks Dave

=A1<>INDIRECT("'[file1.xls]sheet2'!rc",0)