Problem with Error 9 while using files already open

[Olbap]

Hello,

I have a macro that should post a note in a cell wheever this is highlighted in red. The content of the note comes from the value at the same location in another file. I have tried running the code with some parts not active and all seems to come from the 'If' statement, notes appear in all cells if there is no condition. I keep getting a "Subscript out of range" error message.

All the refered files are open while I run the macro and all the sheets have the same name in both files.

Could someone tell me where the problem could be?
Thanks a lot.

Here is the code:


Sub GetDifferences()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim WkSh As String
Dim lastvalue As String
Dim OlFile As String
Dim NwFile As String

'OlFile = Workbooks("Compare2007-08 ENRLPedro.xls").Worksheets("Sheet1").Range("OldFile")
NwFile = Workbooks("Compare2007-08 ENRLPedro.xls").Worksheets("Sheet1").Range("NewFile")

For i = 1 To 10
WkSh = Workbooks("Compare2007-08 ENRLPedro.xls").Worksheets("Sheet1").Cells(i, 8).Value

For j = 1 To 250
For k = 1 To 40
If Workbooks("Compare2007-08 ENRLPedro.xls").Worksheets(WkSh).Cells(j, k).Interior.ColorIndex = 3 Then
lastvalue = CStr(Workbooks(NwFile).Sheets(WkSh).Cells(j, k).Value)
Workbooks("Compare2007-08 ENRLPedro.xls").Worksheets(WkSh).Cells(j, k).NoteText Text:=lastvalue, Start:=1
End If
Next k
Next j
Next i

End Sub

 

[Olbap]

Sorry it seems that I had another bug that was hiding the error 9 message. In fact, in the code, even "lastvalue" cannot take the value from the refered cell.

I am using variables to call the workbooks because I have to do this for a quite big collection of files and it seemed simpler to take the name from a cell. Besides that, files are located in different units..