The reason your program stops is because you use "End", which terminates all
running VBA code. You should use "Exit sub" instead. I cleaned up the code
a little; take a look and let me know if you have any questions.
Public Sub TestViolet1()
Dim wkb As Workbook
Dim wks As Worksheet
For Each wks In wkb.Worksheets(Array("Korea", "China", "Malaysia",
"Brunei"))
Call changes(wks)
Next wks
End Sub
Public Sub Reset(wks As Worksheet)
End Sub
Public Sub changes(wks As Worksheet)
Dim topCel As Range
Dim bottomCel As Range
Dim sourceRange As Range
Dim compareRange As Range
Dim x As Integer
Dim i As Integer
Dim numofRows As Integer
Dim lngRetVal As Long
Call Reset(wks)
'finding last column
If (wks.Cells(1, 24) <> 0) Then ' Dec
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"X2", "X65536", "W2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 23) <> 0 Then ' Nov
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"W2", "W65536", "V2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 22) <> 0 Then ' Oct
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"V2", "V65536", "U2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 21) <> 0 Then ' Sep
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"U2", "U65536", "T2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 20) <> 0 Then ' Aug
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"T2", "T65536", "S2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 19) <> 0 Then ' Jul
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"S2", "S65536", "R2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 18) <> 0 Then ' Jul
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"R2", "R65536", "Q2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 17) <> 0 Then ' May
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"Q2", "Q65536", "P2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 16) <> 0 Then ' Apr
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"P2", "P65536", "O2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 15) <> 0 Then ' Mar
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"O2", "O65536", "N2") = -9) Then Exit Sub
ElseIf wks.Cells(1, 14) <> 0 Then ' Feb
If (Changes_Sub(wks, topCel, bottomCel, sourceRange, compareRange,
"n2", "n65536", "m2") = -9) Then Exit Sub
Else
Exit Sub
End If
numofRows = sourceRange.Rows.Count
'compare the difference and format the row
numofRows = sourceRange.Rows.Count
For i = 1 To numofRows
If ((sourceRange(i) <> compareRange(i)) And (sourceRange(i) <
compareRange(i))) Then
wks.Rows(i + 1).Interior.ColorIndex = 4
Else
If ((sourceRange(i) <> compareRange(i)) And (sourceRange(i) >
compareRange(i))) Then
wks.Rows(i + 1).Interior.ColorIndex = 6
End If
End If
Next
End Sub
Public Function Changes_Sub(wks As Worksheet, topCel As Range, bottomCel As
Range, sourceRange As Range, compareRange As Range, strTopCel As String,
strBottomCel As String, strCompareRange As String) As Long
Changes_Sub = -1
Set topCel = wks.Range(strTopCel)
Set bottomCel = wks.Range(strBottomCel).End(xlUp)
If (topCel.Row > bottomCel.Row) Then
Changes_Sub = -9
Exit Function
End If
Set sourceRange = wks.Range(topCel, bottomCel)
Set compareRange = wks.Range(strCompareRange)
Changes_Sub = 0
End Function