Compare Sheets and highlight differnce

  • Thread starter Thread starter Lavanya Pandian
  • Start date Start date
L

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
 
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.
 
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?
 
Lavanya said:
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
Click to expand...

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
 
Back
Top