conditional formatting referencig another sheet

[neowok]

I have 2 identical sheets, noe an original and one I plan to change. I know its possible to do this using named ranges I am just not sure of the exact wording to make this work

basically i want conditional formatting on sheet 2 to say that

if sheet2!A1 <> Sheet1!A1 then ....

But I want this applied to all cells in an area from N5 to GT64 where the cell on sheet 2 in that range looks at the same cell on sheet 1 in that range and colours it if they are different.

I can set sheet1!N5:GT64 as named range "myrange" but I am not sure how i set the conditional formatting to specifically look at only the corresponding cell on the other sheet rather than the entire range and apply this to all those cells.

this is on excell 2003

[NBVC]

Select N5:GT64 on Sheet2 and invoke Conditional Formatting

Select Formula is from 1st drop down and enter this formula:

=AND(N5<>MyRange,N5<>"")

Then click Format and choose your color.

This will ensure that blanks are not highlighted also.

[neowok]

thanks, tried that but it only checks if every cell on sheet2 is <> the first cell of Myrange rather than checking if the cell is the same as the corresponding cell within Myrange.

i.e. it isnt doing
if sheet2!N5 <> sheet1!N5 the colour it
if sheet2!N6 <> sheet1!N6 the colour it
if sheet2!N7 <> sheet1!N7 the colour it

its doing
if sheet2!N5 <> sheet1!N5 the colour it
if sheet2!N6 <> sheet1!N5 the colour it
if sheet2!N7 <> sheet1!N5 the colour it

It also creates a delay when switching TO the sheet with the conditional formatting on, I have a lot of cells with it on but havent seen conditional formatting create any delay before, it might be because its wrongly colouring most of the cells I guess.

[Shijesh Kumar]

I'm not sure about this..
but generally excel 2003 does not allow you to reference another sheet in conditional formula..

You have to use named range to reference to another sheet

[NBVC]

thanks, tried that but it only checks if every cell on sheet2 is <> the first cell of Myrange rather than checking if the cell is the same as the corresponding cell within Myrange.

i.e. it isnt doing
if sheet2!N5 <> sheet1!N5 the colour it
if sheet2!N6 <> sheet1!N6 the colour it
if sheet2!N7 <> sheet1!N7 the colour it

its doing
if sheet2!N5 <> sheet1!N5 the colour it
if sheet2!N6 <> sheet1!N5 the colour it
if sheet2!N7 <> sheet1!N5 the colour it

It also creates a delay when switching TO the sheet with the conditional formatting on, I have a lot of cells with it on but havent seen conditional formatting create any delay before, it might be because its wrongly colouring most of the cells I guess.

No it should check the corresponding cells...because I did not make N5 absolute by adding the $ signs.. check the formula in CF again and make sure there are no double quotes around it.

[neowok]

The formula in CF starting at N5 is =N5<>Myrange

The CF formula in N6 DOES change to =N6<>Myrange that isnt the problem

but it only seems to look at the first cell in Myrange which is N5 on the other sheet

[martindwilson]

try
highlighting on sheet 2 n5:gt64
conditional format
formula is

=INDEX(myrange,ROW()-4,COLUMN()-13)<>N5

[neowok]

aha yes that has worked thanks

[NBVC]

Try reselecting the range and applying this CF formula instead

Please Login or Register  to view this content.