Compare 2 spreadsheets and highlight differences

1cyril1

Board Regular
Joined
Mar 31, 2003
Messages
89
I did a search for compare threads and there are numerous posts but I didn't manage to find one of the more simpler tasks covered - I'm sure they are hiding in there somewhere! If you can point me towards one that meets my needs or if you can suggest a solution via Reply post I'd be very grateful!

I've 2 spreadsheets as follows:

A) cols A to Z (a master doc that requires updating weekly)
B) cols A to G (updates downloaded from a work database) - all 7 cols have equivalent cols in sheetA though not adjacent

One of the cols in A and B contains the key field that's used to check for a match between the 2 sheets (in SheetA it's Col B and in SheetB it's Col A).

There could be over 4,000 records/rows in SheetA but SheetB will typically have less than 500 rows. I want to run a compare between the 2 sheets weekly and:


  1. Check each record in SheetB (ColA) against all of the records in SheetA (ColB) based on the key field.
  2. If there's no matching key in the weekly download (SheetB-ColA), anywhere in SheetA (ColB), then there is no updating to be done for that row and move on to the next record in B.
  3. Where a match is found on the key field in SheetA and SheetB, then the row in SheetB is to be highlighted (ideally the rows would be copied to a new worksheet as a record of that week's changes). Then, the other 6 fields in SheetB would replace the corresponding 6 fields in SheetA, even if only one of the fields is different. For the 6 non-key fields, let's assume that SheetB_Cols B to G correspond with SheetA_Cols C, E, G, I, K, M

I hope I've explained what's required reasonably clearly. I've used the terms record/row and field/cell interchangeably. The spreadsheets have yet to be created so I can't provide samples. However, I know the cols to be matched won't appear in the same order and I've used an illustrative example. To summarise the above:

If cell A1 in SheetB matches any ColB cell in Sheet A, then the data in B1, C1, D1, E1, F1 and G1 is highlighted and cells in the corresponding SheetA row are replaced by the SheetB data.

I look forward to any guidance you may be able to provide.

Many Thanks!
Cyril
 
Last input as midnight approaches here ....

In your post#51 you said this which I did correctly: in sheet 2 of the today spreadsheet in cell Q1 enter this formula =SUMPRODUCT(($A$1:$A$1000<>"")*1)+1 I did this and now it's overwritten after the macro ran and the highlighted row is pasted in row1 (before macro dies)

How would I know/see the values of j and z?? Do you mean somewhere in the macro debugger which I don't know much about or .... have they a location in the spreadsheet?

(I saved both the original and final versions at the link I provided above - much better than trying to explain long-hand and relevant aspects can be brought into this conversation for wider forum sharing?)

Take care and a big 'thank you' until the next time!
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Can you please check that your macro is exactly the same as the one in post 50 - as before then I noticed a counter was being over-written. There is not that much time difference between Dublin, and Kettering UK....
 
Upvote 0
I always do copy&paste direct from your posts so should be identical (as per post#50) ... re-pasting here from todaydata for checking .... just in case:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 22/11/2017 by bob
'




'
Range("A1:X1000").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("AA1:Ax1000").Select
ActiveSheet.Paste
For j = 2 To Cells(1, 53)
For Z = 2 To Cells(1, 26)
Cells(30, 9) = j
Cells(31, 9) = Z
For Delay = 1 To 100000000: Next Delay
If Cells(Z, 1) = Cells(j, 27) Then GoTo 20
15 Next Z
Cells(j, 38) = 1
Cells(j, 27).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
GoTo 80
20 For y = 2 To 11
If Cells(Z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
30 Cells(Z, y).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, y + 26).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(Z, 12) = 1
Cells(j, 38) = 1
40 Next y
80 Next j
Range("AA1:AL13").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("a1:l13").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("AA1:AL13").Select
Selection.ClearContents
ActiveWindow.ActivateNext
For myrow = 2 To 13
If Cells(myrow, 12) = 1 Then GoTo 100 Else GoTo 200
100 Rows(myrow).Select
Selection.Copy
Sheets(2).Select
Rows(Cells(1, 17)).Select
ActiveSheet.Paste
Sheets(1).Select
200 Next myrow
Sheets(1).Select
999 End Sub
 
Upvote 0
please change it to this and try again

Range("A1:k2000").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("AA1:AK2000").Select
ActiveSheet.Paste
For j = 2 To Cells(1, 43)
For z = 2 To Cells(1, 17)
Cells(30, 8).Select
Cells(30, 9) = j
Cells(31, 9) = z
For delay = 1 To 10000000: Next delay
If Cells(z, 1) = Cells(j, 27) Then GoTo 20
15 Next z
Cells(j, 38) = 1
Cells(j, 27).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
GoTo 80
20 For y = 2 To 11
If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
30 Cells(z, y).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, y + 26).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, 38) = 1
40 Next y
80 Next j
Range("AA1:AL2000").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("a1:l2000").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("AA1:AL2000").Select
Selection.ClearContents
ActiveWindow.ActivateNext
For myrow = 2 To Cells(1, 17)
If Cells(myrow, 12) = 1 Then GoTo 100 Else GoTo 200
100 Rows(myrow).Select
Selection.Copy
Sheets(2).Select
Rows(Cells(1, 17)).Select
ActiveSheet.Paste
Cells(myrow, 12).Select
Selection.ClearContents
Sheets(1).Select
200 Next myrow
Sheets(1).Select
999 End Sub
 
Upvote 0
note lines 9 and 10 are the J and Z values - and you can shorten or lengthen the delay by adding or removing zeroes in line 11
 
Upvote 0
[TABLE="width: 695"]
<colgroup><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]in today sheet 2 A1 to K1 put the headers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]in Q1 of today sheet 2 put[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=SUMPRODUCT((A1:A2000<>"")*1)+1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]in Q1 of yesterday put '=SUMPRODUCT((A1:A2000<>"")*1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]in AQ1 of yesterday put '=SUMPRODUCT((AA1:AA2000<>"")*1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 1036"]
<colgroup><col span="5"><col><col span="6"><col><col><col></colgroup><tbody>[TR]
[TD]Con Number (key)[/TD]
[TD]Tr Code[/TD]
[TD]C[/TD]
[TD]Start Date[/TD]
[TD]Fin Date[/TD]
[TD]Approved Value[/TD]
[TD]Used Value[/TD]
[TD]Ven Numb[/TD]
[TD]Ven Name[/TD]
[TD]BU[/TD]
[TD]REF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]NNN[/TD]
[TD]ZZZ[/TD]
[TD]NNN[/TD]
[TD]PPP[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]YYY[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]XXX[/TD]
[TD]VVV[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]operation is[/TD]
[TD]COMPLETE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]WWW[/TD]
[TD]TTT[/TD]
[TD]SSS[/TD]
[TD]RRR[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]555[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]in the today sheet1 shown above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]all cells are highlighed correctly[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]note only key 110 row matches yesterday with no discrepancies[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]so all rows except 110 have been copied to sheet 2 of today workbook[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]in M7 enter "operation is"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]and conditionally format it to white font if N7 is empty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]the macro puts "COMPLETE" in N7 when it has finished[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the macro below has been slightly tweaked[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]and I have added comments to explain what is happening[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]I also speeded the macro up but you can still see the J and Z cycling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Range("A1:k2000").Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Selection.Copy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]ActiveWindow.ActivateNext[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]'move to yesterday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Range("AA1:AK2000").Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]ActiveSheet.Paste[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]'put a copy of today in the yesterday workbook[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]For j = 2 To Cells(1, 43)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]For z = 2 To Cells(1, 17)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cells(30, 9) = j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cells(31, 9) = z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]For delay = 1 To 5000000: Next delay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]If Cells(z, 1) = Cells(j, 27) Then GoTo 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]'checks if keys are the same[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15 Next z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cells(j, 38) = 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cells(j, 27).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]With Selection.Interior[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"].ColorIndex = 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"].Pattern = xlSolid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End With[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GoTo 80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]20 For y = 2 To 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]'highlights both cells if there ia a discrepancy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]30 Cells(z, y).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]With Selection.Interior[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"].ColorIndex = 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"].Pattern = xlSolid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End With[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(j, y + 26).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]With Selection.Interior[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"].ColorIndex = 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"].Pattern = xlSolid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End With[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cells(j, 38) = 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]'marks the row with a "1"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40 Next y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]80 Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]'copies the temporary today data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Range("AA1:AL2000").Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Selection.Copy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]ActiveWindow.ActivateNext[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]'move to today[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Range("a1:l2000").Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]ActiveSheet.Paste[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]'overwrites the original today data with the "updated" temporary today from yesterday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]ActiveWindow.ActivateNext[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]'move to yesterday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Range("AA1:AL2000").Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Selection.ClearContents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]'delete the temporary today data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]ActiveWindow.ActivateNext[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]'move to today[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sheets(1).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]For myrow = 2 To Cells(1, 17)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]If Cells(myrow, 12) = 1 Then GoTo 100 Else GoTo 200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]'following code copies only rows with at least one cell highlighted to sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]100 Rows(myrow).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Selection.Copy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sheets(2).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]'open sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Rows(Cells(1, 17)).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]ActiveSheet.Paste[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sheets(1).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(myrow, 12).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Selection.ClearContents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]'deletes the 1 in column 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]200 Next myrow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sheets(2).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Columns(12).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Selection.ClearContents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]'clears all the 1's in column 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sheets(1).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(7, 14) = "COMPLETE"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cells(7, 14).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]999 End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
That's an amazing piece of work, really appreciate it! The comments are a good addition even if I haven't got to check them all yet.

Now for the feedback from my testing tonight (on my Mac):

a) the macro runs without failing for the first time; ends as you suggest in N7 of todaydata/sheet#1 with "COMPLETE"
b) highlighting appears to be working ok
c) much quicker timescale ..... especially when I amended down to 50000
d) nothing written to todaydata/sheet#2

Can you please clarify/confirm the following:

1)
in today sheet 2 A1 to K1 put the headers ..... do you mean just paste in the same heading for each col as in sheet#1? (I did that)
2)
in M7 enter "operation is" and conditionally format it to white font if N7 is empty .... what does "operation is" mean? it doesn't seem to be a cond formatting option? should it be 'N7' by any chance?
3)
you can still see the J and Z cycling .... I see nothing moving anywhere (have the 2 workbooks open side-by-side) - what should I see?
4)
'clears all the 1's in column 12 ..... the '1's are still there and I'd like to keep them to enable filtering on changed rows, ok?
5) Q1 and AQ1 entries in yesterday .... assuming sheet#1?
6) finally .... how low can I go with the delay number e.g. 10? (are they milliseconds?)

I'm hoping I can get to try in Windows tomorrow ..... fingers crossed!


P.S. I assume you realise I can't see the highlighting you report for your data at the top ..... just to be fully clear ... happy that you can see it on your off-line version!
 
Last edited:
Upvote 0
d) today sheet 2 is filled correctly at this end

1 yes just put the headers in - they will stay there for ever then
2 it is just a message to you to confirm job is complete
3 adjust font sizes in yesterday so that columns A to AZ are visible. have both workbooks open but only today on screen and run the macro from today - it will move you to yesterday which stays on screen until macro ends when it returns you to today
4 ok I will leave the 1's
5 yesterday has only one sheet
6 the smaller the number in the delay loop the faster it runs. it is there for testing purposes- it will be removed eventually
ps you should be seeing the highlighting - see your b above - you say you can see it....
 
Upvote 0
I have just spent an hour further tweaking the macro and adding a few more comment lines. It now does everything you ask for. At the start I have both workbooks open, but today sheet 1 selected. When I run the macro from there it updates yesterday with cell highlighting and 1's in last column. Today sheet 1 is also highlighted and marked with 1's, and only rows with 1's are copied to today sheet 2

below is macro as of Dec 20 12:35 pm

Range("A1:k2000").Select
Selection.Copy
ActiveWindow.ActivateNext
'move to yesterday
Range("AA1:AK2000").Select
ActiveSheet.Paste
'put a copy of today in the yesterday workbook
For j = 2 To Cells(1, 43)
For z = 2 To Cells(1, 17)
Cells(30, 9) = j
Cells(31, 9) = z
'this shows how J and Z cycle (for diagnostic purposes)
For delay = 1 To 5000000: Next delay
If Cells(z, 1) = Cells(j, 27) Then GoTo 20
'checks if keys are the same
'if keys are not the same try the next key in yesterday
15 Next z
'if no key in yesterday matches, mark the temporary today sheet with a 1 in the last column
'also highlight the key in temp today data
Cells(j, 38) = 1
Cells(j, 27).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'now run the next today key
GoTo 80
'keys match so now look at each cell in the rows
20 For y = 2 To 11
If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
'highlights both cells if there ia a discrepancy
30 Cells(z, y).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(z, 12) = 1
'marks the yesterday row with a 1
Cells(j, y + 26).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, 38) = 1
'marks the row with a "1"
40 Next y
80 Next j
'copies the temporary today data
Range("AA1:AL2000").Select
Selection.Copy
ActiveWindow.ActivateNext
'move to today
Range("a1:l2000").Select
ActiveSheet.Paste
'overwrites the original today data with the "updated" temporary today from yesterday
ActiveWindow.ActivateNext
'move to yesterday
Range("AA1:AL2000").Select
Selection.ClearContents
'delete the temporary today data
ActiveWindow.ActivateNext
'move to today
Sheets(1).Select
For myrow = 2 To Cells(1, 17)
If Cells(myrow, 12) = 1 Then GoTo 100 Else GoTo 200
'following code copies only rows with at least one cell highlighted to sheet 2
100 Rows(myrow).Select
Selection.Copy
Sheets(2).Select
'open sheet 2
Rows(Cells(1, 17)).Select
ActiveSheet.Paste
Sheets(1).Select
200 Next myrow
Sheets(2).Select
Range("m1:p2000").Select
Selection.ClearContents
Cells(40, 1).Select
Sheets(1).Select
Cells(7, 14) = "COMPLETE"
Cells(7, 14).Select
999 End Sub
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top