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

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



"RFJ" wrote in message
...
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

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"RFJ" wrote in message
...
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

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



RFJ wrote:

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

--

Dave Peterson

Splendid, Dave ! Thanks.
Direct, simple way to do it using CF.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

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




"Dave Peterson" wrote in message
...
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



RFJ wrote:

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

--

Dave Peterson