Hi All
I have to reconcile the data for two worksheets 'Master' and 'Change Log'. If the cells in the columns A, B and F of the Change Log equal to Master cells value in columns A, B, F, then it has to reconcile the rest of cells in columns C, D, G, H, I and J . If the cells in any columns C, D, G, H, I and J of the Master don't equal to the Change log cell value, then Master cell value will have to be replaced by the Change log cell value, and the Change log cell will be highlighted in yellow.
I have drafted the macro below, however there is an error message 'Compile error: Invalid Next control variable reference' and the error highlighted the 'Next i'
Please could any one advise, many thanks.
I have to reconcile the data for two worksheets 'Master' and 'Change Log'. If the cells in the columns A, B and F of the Change Log equal to Master cells value in columns A, B, F, then it has to reconcile the rest of cells in columns C, D, G, H, I and J . If the cells in any columns C, D, G, H, I and J of the Master don't equal to the Change log cell value, then Master cell value will have to be replaced by the Change log cell value, and the Change log cell will be highlighted in yellow.
I have drafted the macro below, however there is an error message 'Compile error: Invalid Next control variable reference' and the error highlighted the 'Next i'
Please could any one advise, many thanks.
VBA Code:
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Reconcile()
Dim ws1 As Worksheet
Dim LastRow As Long
Dim LRow As Long
Dim i As Long
Dim r As Long
Set ws = Worksheets("Master")
Set ws1 = Worksheets("Change Log")
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
LRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For i = LRow To 2 Step -1
For r = LastRow To 2 Step -1
If ws1.Cells(i, "A").Value = ws.Cells(r, "A").Value And ws1.Cells(i, "B").Value = ws.Cells(r, "B").Value And ws1.Cells(i, "F").Value = ws.Cells(r, "F").Value Then
If ws1.Cells(i, "C").Value <> ws.Cells(r, "C").Value Then
ws1.Cells(i, "C").Interior.ColorIndex = 6 And ws.Cells(i, "C").Value = ws1.Cells(i, "C").Value
If ws1.Cells(i, "D").Value <> ws.Cells(r, "D").Value Then
ws1.Cells(i, "D").Interior.ColorIndex = 6 And ws.Cells(i, "D").Value = ws1.Cells(i, "D").Value
If ws1.Cells(i, "G").Value <> ws.Cells(r, "G").Value Then
ws1.Cells(i, "G").Interior.ColorIndex = 6 And ws.Cells(i, "G").Value = ws1.Cells(i, "G").Value
If ws1.Cells(i, "H").Value <> ws.Cells(r, "H").Value Then
ws1.Cells(i, "H").Interior.ColorIndex = 6 And ws.Cells(i, "H").Value = ws1.Cells(i, "H").Value
If ws1.Cells(i, "I").Value <> ws.Cells(r, "I").Value Then
ws1.Cells(i, "I").Interior.ColorIndex = 6 And ws.Cells(i, "I").Value = ws1.Cells(i, "I").Value
If ws1.Cells(i, "J").Value <> ws.Cells(r, "J").Value Then
ws1.Cells(i, "J").Interior.ColorIndex = 6 And ws.Cells(i, "J").Value = ws1.Cells(i, "J").Value
End If
End If
End If
End If
End If
End If
End If
Next i
Next r
End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]