Compare 2 worksheets of same size and highlight changes in different colour

[Jessie15]

I am completely new to VBA. I have 2 worksheets of the same size (about 4000 rows), cells containing either text, time or date data. I need to compare the new worksheet with the old and highlight anything that has changed on the new with a different colour. I currently do the exercise in Excel by creating a 3rd worksheet and then using the formula IF('new'!B2='old'B2,TRUE,'old'!B2) but this isn't ideal as the data that has stayed the same is replaced by 'TRUE' and the worksheet is therefore only partially useful. Trying to figure it out in VBA and failing miserably.

Any help (in very simplistic language please!) would be greatly appreciated.

 

[jc.021286]

It depends on how you approach this, doing bank rec's or stock option reconciliations I would do this with about the same amount of data. What you need to realize,
1. if there is an extra row or line item it will throw almost everything off because you are comparing 1 line to 1 line etc.
2. Sorting can create a nightmare, especially if 1 text field has an accidental extra space, you're screwed.

I've tried automating though have normally resorted to the following:
If your data in each runs from A1:D4000 in E1:E4000 have the following formula
=if(D1=C:/[a.xls]Sheets1!D1,"","<<<< ERROR PLEASE REVIEW")

Similar to what you have done in the past and this will flag cells that there are discrepancies in, all that are okay will be blank. If you come across something where you are missing a line item, you would need to insert that data line and fill down, or recognize the error and adjust the formula 1 row and fill down to continue.

Takes a lot less time due to the cell by cell matching being so finicky if you are missing or a line item.

May not apply to your data however, let me know if this does not work, if you want to still resort to VBA, we can do something.

jc

 

[VoG]

This might be of interest http://download.cnet.com/Excel-Overlay/3000-2077_4-10963782.html

 

[Norie]

Why not just change TRUE to 'new'!B2 in the formula?

 

[Jessie15]

Thanks everyone.
JC - I tried your suggestion, but it leaves me in the same situation as the method I already use. It flags up all the lines where there's a discrepancy, but I have to still refer to the old spreadsheet to establish what's changed. Do you know any coding where, if the cell doesn't equal the same cell on the old worksheet, it will change to a different colour?

Norie - I'd tried your idea myself, but it ends up giving me a worksheet exactly like my 'new' worksheet, with nothing highlighting what's different between the two.

To explain, the spreadsheet is information on passengers' flight and hotel information, and is what is used to book transfers from the airport to the hotels. When a flight schedule changes or a hotel changes, I need to be able to easily identify the change to the transportation company, rather than just send them the updated spreadsheet and let them trawl through 3000 records to find the differences.

VoG - I'm tempted by your suggested download, but I sort of wanted to figure it out without cheating! Although I guess I'm cheating asking the forum for help....

 

[Norie]

What you originally posted didn't highlight anything.

Personally, and I've done this in the past, I would copy both sets of data to an new worksheet and place them either side by side or one above the other.

That would make them far easier to compare.

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 width=64>Field1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=64>Field2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=64>Field3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=64>Field4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=64>Field5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: lime; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>Field1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>Field2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>Field3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>Field4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>Field5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 height=17 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl22 align=right x:num>8</TD></TR></TBODY></TABLE>

 

[Jessie15]

Thanks Norie - that gives a better result than my current TRUE/FALSE method but not quite the result I was after. How did you do it though? I tried conditional formatting and got error messages about referencing a range of data. I'd set the cell formulas for 1st set to equal the 2nd set (which I'd copied alongside as you suggested), namely =$W:$AR.
Thank you.

 

[djreiswig]

How about just a simple loop to go thorough the sheets and see if the values are equal in each cell?

Sub comparesheets()
    For Each cl In Sheets("sheet2").UsedRange
        If cl.Value <> Sheets("Sheet1").Cells(cl.Row, cl.Column) Then
            cl.Interior.Color = RGB(0, 0, 255)
        End If
    Next cl
End Sub

 

[Jessie15]

Hi djreiswig
Genius - the perfect solution. You wouldn't believe the blood, sweat and tears I've shed over this in the past. Just a tiny bit of coding and so effective - I love it!

 

[djreiswig]

It's the simple things that are so often overlooked. I'm not sure it is the most elegant solution, but Im glad it worked for you.