The macro below works sometimes, sometimes it doesn't. I can run it and some
cells will be updated and others that should have been updated aren't. I can
run it a second time and it'll update some of those cells that didn't get
updated the first time. What's going on here? Am I not clearing some sort of
buffer or what? Please help - people are waiting on a fix and I don't have
a clue.



Thanks







Sub Netcheck()

Application.StatusBar = "Processing crosscheck Macro"

MYFILE1 = "List of customers.xls"

Myfile2 = "My other list of customers.xls"



CcNC1 MYFILE1, Myfile2

CcNC2 MYFILE1, Myfile2

CcNC3 MYFILE1, Myfile2

CcNC4 MYFILE1, Myfile2

CcNC5 MYFILE1, Myfile2

CcNC6 MYFILE1, Myfile2

CcNC7 MYFILE1, Myfile2

CcNC8 MYFILE1, Myfile2



CcNC10 MYFILE1, Myfile2

CcNC11 MYFILE1, Myfile2

CcNC12 MYFILE1, Myfile2

Application.StatusBar = "Macro completed"

MsgBox "Macro completed"

Application.StatusBar = ""

End Sub





'-------------------------- Batch 1 -------------------------------

Sub CcNC1(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 1"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 1").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1101" Then

c.Offset(0, 35) = strname.Offset(0, 24)

With c.Offset(0, 35).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 2 -------------------------------

Sub CcNC2(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 2"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 2").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1102" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 3 -------------------------------

Sub CcNC3(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 3"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 3").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1103" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 4 -------------------------------

Sub CcNC4(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 4"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 4").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1104" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 5 -------------------------------

Sub CcNC5(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 5"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 5").Range("H2:H10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1105" Then

c.Offset(0, 33) = strname.Offset(0, 24)

With c.Offset(0, 33).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 6 -------------------------------

Sub CcNC6(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 6"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 6").Range("H2:H10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1106" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 7 -------------------------------

Sub CcNC7(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 7"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 7").Range("G2:G10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1107" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 8 -------------------------------

Sub CcNC8(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 8"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 8").Range("G2:G10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1108" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'--------- Batch 9 doesn't have the Site Conversion Date -----------

'-------------------------- Batch 10 -------------------------------

Sub CcNC10(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 10"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 10").Range("E2:E10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1110" Then

c.Offset(0, 37) = strname.Offset(0, 24)

With c.Offset(0, 37).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 11 -------------------------------

Sub CcNC11(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 11"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 11").Range("K2:K10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1111" Then

c.Offset(0, 34) = strname.Offset(0, 24)

With c.Offset(0, 34).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub

'-------------------------- Batch 12 -------------------------------

Sub CcNC12(MYFILE1, Myfile2)

Application.StatusBar = "Processing Batch 12"

For Each strname In Workbooks(MYFILE1).Sheets(1).Range("M2:M10000")

With Workbooks(Myfile2).Sheets("Batch 12").Range("I2:I10000")

Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If strname.Offset(0, -12) = "MYFILE1112" Then

c.Offset(0, 36) = strname.Offset(0, 24)

With c.Offset(0, 36).Interior

.ColorIndex = 6

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

End If

End If

End With

Next

End Sub