Run time error 9 - Subscript Out of Range Error

gertie193

Board Regular
Joined
Feb 28, 2008
Messages
82
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:

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
Code:
Sheets(3). Range(Cells, etc.)
and
Code:
ThisWorkbook.Sheets(3).Range(Cells, etc.)
, but they cause the same Run Time 9 error but it highlights the
Code:
lastrowdest = Sheets(3), etc.
in the beginning.

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

Code:
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,111
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.
 
Last edited:

gertie193

Board Regular
Joined
Feb 28, 2008
Messages
82
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.
 

Forum statistics

Threads
1,136,909
Messages
5,678,517
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top