Run time error 9 - Subscript Out of Range Error

[gertie193]

I use Excel 2016 Office 365 and I have been trying to fix why my code has a Run time error 9 - Subscript Out of Range Error on this line of code:

 ActiveSheet.Range(Cells(i, "A"), Cells(i, lastcoldest)).Interior.ColorIndex = RGB(255, 242, 204)

I'm having the code to compare the data in column A between 2 workbooks, wkorig (the origin workbook, which is in a folder on the desktop called MRFolder), and wkdest (the destination workbook), and wherever there is a match in values to highlight the row in the destination workbook. It worked once and just highlighted the first row, now I get this error.

I'm trying to learn more about VBA and I don't see why I get that error b/c the code works to open the destination workbook, and I am just asking it to look on that active sheet and highlight the row if there is a match.

I've changed this code to say

Sheets(3). Range(Cells, etc.)

and

ThisWorkbook.Sheets(3).Range(Cells, etc.)

, but they cause the same Run Time 9 error but it highlights the

lastrowdest = Sheets(3), etc.

in the beginning.

Does someone know how to fix this type of error? Thanks.

Option Explicit


Sub highlightrow()


Dim wkdest As Workbook
Dim wkorig As String
Dim i As Long, j As Long, lastrowdest As Long, lastroworig As Long, lastcoldest As Long
Dim criteria As String
Dim pathstr As String


Set wkdest = ThisWorkbook
lastrowdest = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
lastcoldest = Sheets(3).Cells(2, Columns.Count).End(xlToLeft).Column
'gets filename from path
wkorig = Dir("C:\Users\PC-1\Desktop\MRFolder\AAA_data.xlsm")
pathstr = "C:\Users\PC-1\Desktop\MRFolder\"




Application.ScreenUpdating = False


'This gets criteria, node name, from wkdest workbook
For i = 2 To lastrowdest
criteria = wkdest.Sheets(3).Cells(i, "A").Value
       'This is to just get row count for wkorig file, AAA_data.xlsm
       If Len(wkorig) = 0 Then
           Exit Sub
       Else
           Workbooks.Open Filename:=pathstr & "\" & wkorig
           Sheets(1).Activate
           lastroworig = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
       End If
    
    For j = 2 To lastroworig
        If Sheets(1).Cells(i, "A").Value = criteria Then
            'activate the destination workbook in order to highlight the row where the values match
            wkdest.Sheets(3).Activate
            ActiveSheet.Range(Cells(i, "A"), Cells(i, lastcoldest)).Interior.ColorIndex = RGB(255, 242, 204)
        End If
    Next j
Next i




Application.ScreenUpdating = True
    
End Sub

 

[Logit]

Are your sheets in the order specified in your code ?

Sheets(1) ... that needs to be the FIRST sheet tab in your workbook, all the way to the LEFT.

Sheets(3 ... that needs to be the THIRDS sheet tab in your workbook, two tabs to the right of the FIRST.


If you are wanting to refer to them by NAME: Sheets("Sheet1") .... Sheets("Sheet2")

Also, it may not be necessary to refer to each individual sheet if you are using "ActiveSheet". Mixing the two may cause issues.

 

[gertie193]

Thank you, Logit. Yes, my sheets are in that order. I've tried different codes on that line of code, but I'm wondering if I see something else as the problem. I will work on it more.

Thank you, again.