In the code below I get message "Subscript out of range Runtime Error 9"
I think it's to do with the asterisked line, not too sure.
The only worksheets I have are "J" AND "J2"

Sub desI()
Worksheets.Add.Name = "Changes"
k = 2
For i = 1 To 200
For j = 1 To 200
** If Worksheets("J").Cells(i, j) < Worksheets("J2").Cells(i, j) Then
Worksheets("J").Cells(i, j).EntireRow.Copy _
Destination:=Worksheets("Changes").Cells(k, 1)
Worksheets("J2").Cells(i, j).EntireRow.Copy _
Destination:=Worksheets("Changes").Cells(k + 1, 1)
With Worksheets("Changes")
.Cells(k + 1, 1).ClearContents
For m = 2 To 20
.Cells(k + 2, m).Value = .Cells(k, m) - .Cells(k + 1, m)
With .Cells(k + 2, m).borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next m
End With
k = k + 4
Exit For
End If
Next j
Next i

End Sub

Are you sure that 'J' and 'J2' are the actual names? No spaces?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"teresa" wrote in message
...
In the code below I get message "Subscript out of range Runtime
Error 9"
I think it's to do with the asterisked line, not too sure.
The only worksheets I have are "J" AND "J2"

Sub desI()
Worksheets.Add.Name = "Changes"
k = 2
For i = 1 To 200
For j = 1 To 200
** If Worksheets("J").Cells(i, j) < Worksheets("J2").Cells(i,
j) Then
Worksheets("J").Cells(i, j).EntireRow.Copy _
Destination:=Worksheets("Changes").Cells(k, 1)
Worksheets("J2").Cells(i, j).EntireRow.Copy _
Destination:=Worksheets("Changes").Cells(k + 1, 1)
With Worksheets("Changes")
.Cells(k + 1, 1).ClearContents
For m = 2 To 20
.Cells(k + 2, m).Value = .Cells(k, m) - .Cells(k + 1, m)
With .Cells(k + 2, m).borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next m
End With
k = k + 4
Exit For
End If
Next j
Next i

End Sub