Compare Sheets and highlight differnce

[Lavanya Pandian]

Hi All,

Is there a way to compare contents between two sheets, and use conditional
formatting to highlight the difference without using Macro? I am looking at
data of A10:T140

 

[Jacob Skaria]

Create a named range for the data in Sheet2 (say name the range in Sheet2
A10:T40 as myRange) ...(From menu Insert>Name>Define...)

1. Select the cell/Range (say A10:T40). Please note that the cell reference
A10 mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=A10<>INDEX(myRange,ROW(A1),COLUMN(A1))

4. Click Format Button>Pattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

 

[Lavanya Pandian]

It is not working for me. I use XL2007, and i want contents in sheet2 to be
highlighted if it does not match with Sheet 1 contents.
1. I named the range in Sheet 1 as my range (A10:T40)
2. Selected A10:T40 in Sheet 2 with A10 as active cell
3. I clicked Conditional formatting->New Rule-> Use a Formula...
4.Typed A10<>INDEX(myRange,ROW(A1),COLUMN(A1)), and selected Red color

Nothing happened. Did I miss something?

 

[bala_vb]

Hi All,

Is there a way to compare contents between two sheets, and us
conditional
formatting to highlight the difference without using Macro? I a
looking at
data of A10:T140

conditional formatting doesnt support to compare the values in othe
sheet.

So you can copy the contents of other sheet (sheet2) somewhere besid
sheet1 and use conditional formatting to compare.

all the bes