Compare Sheets and highlight differnce
 

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

Compare Sheets and highlight differnce
 

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

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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)


"Lavanya Pandian" wrote:

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

Compare Sheets and highlight differnce
 

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?



"Jacob Skaria" wrote:

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

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 FormatConditional Formatting

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

4. Click Format ButtonPattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)


"Lavanya Pandian" wrote:

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

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

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

all the best