Compare Data in one cell to another cell (but for LOTS of cells)

[RayJay]

Hi all,

I have a spreadsheet which looks like this:

<TABLE style="WIDTH: 171pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=225 border=0 x:str><COLGROUP><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" span=9 width=25><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right width=25 height=18 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=25></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 19pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=25 x:num>4</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">B</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">C</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">D</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">W</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">X</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Y</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Z</TD></TR></TBODY></TABLE>

which goes 97 rows down, and I was wondering: is there an easy way to compare the data which is in 1 on the left (i.e. A) to the data which is in 1 on the right (i.e. W), to compare the data which is in 2 on the left (i.e. B) to the data which is in 2 on the right (i.e. X) and so on...

I also want it to colour the cells Green if they match (e.g. 1 on the left and 1 on the right have the same data in them) and colour the cells Red if they don't match.

I know I can do this with conditional formatting, but that would take a very long time (since there are 388 cells to compare to another 388), and it would probably be easier just to compare them myself, so if anyone knows of a really easy way to do this, or has a macro which will do this, then I would be very happy!

Any help would be appreciated.

Thanks very much,

RayJay

(P.S.) The data in the cells is not just numerical: it can either be numerical or alphabetical.

(P.P.S.) I'm using Excel 2003.

 

[texasalynn]

is this what you want?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	*	1	2	3	4	TRUE	TRUE	TRUE	TRUE
2	A	B	C	D	*	W	X	Y	Z	FALSE	FALSE	FALSE	FALSE
Sheet3

if you put this formula in conditional format then make the condition for true the green and red for false

PS, I know you were probably wanting the cells compared to A & W, but was keeping the display simple. Just change the formulas to the proper cell setting

 

[RayJay]

Thank you ever so much - that's absolutely perfect!!

Cheers texasalynn!

RayJay