Prev: Macros in teplates
Next: Averaging weekly data into Months
From: Lavanya Pandian on 19 May 2010 03:41
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
From: Jacob Skaria on 19 May 2010 04:51
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.

--
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
From: Lavanya Pandian on 20 May 2010 05:56
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 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.
>
> --
> 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
From: bala_vb on 20 May 2010 09:25

Lavanya Pandian;954584 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




--
bala_vb
 | 
Pages: 1
Prev: Macros in teplates
Next: Averaging weekly data into Months