Run-time error '9': Subscript out of range

[SOLVED]I am trying to run the following code out of my personal workbook. It works on any new workbook I create, but not on any previous workbook (still xlsx) that I open.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Sub HighlightRow()
' Highlight the entire row of the cell selected

Application.ScreenUpdating = False
 
' Remove the Conditional formatting and "screen updating" code from the active sheet (toggle off)   
    For i = 1 To Cells.FormatConditions.Count    ' loops through all conditional formatting rules
        If Cells.FormatConditions(i).Formula1 = "=CELL(""row"")=ROW()" Then
            Cells.FormatConditions(i).Delete
            With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
                On Error GoTo ExitS
                .DeleteLines 1, [5]
            End With
ExitS:
            Exit Sub
            On Error Resume Next
        End If
    Next i
 
' if the above rule is not found, inserts rule and "screen updating" code into activesheet (toggle on)   
    Cells.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CELL(""row"")=ROW()"
    Cells.FormatConditions(Cells.FormatConditions.Count).SetFirstPriority
    With Cells.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Cells.FormatConditions(1).StopIfTrue = False
    
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
        .InsertLines Line:=.CreateEventProc("SelectionChange", "Worksheet") + 1, _
                     String:=vbCrLf & "    application.screenupdating = True"
    End With
    Application.VBE.MainWindow.Visible = False

Application.ScreenUpdating = True

End Sub

Sorry if this is a bit messy. Any help would be appreciated.

Hello ndsutherland,

If the workbooks you are opening are xlsx then the code will fail. The reason is an xlsx workbook does not contain VBA macros and therefore no code modules.

Thank you for responding.

I am running this code out of "personal.xlsb," and it works in any .xlsx file I create, close, and reopen but not on previously created xlsx or xlsm files.

What are you trying to do with this code?

It appears you are trying to alter code in a workbook and apply conditional formatting in the same workbook.

Why do you need to insert/delete code to do that?

Without the code I have to manually recalculate every time I select a different cell for the conditional formatting to take effect.

I believe I got it working. I had to do some research on extensibility on the CPearson site. I'm not a big fan of enabling access to the VBA project, but I added a reminder to disable the checkbox.

Before running this code, enable access to the vba project model and enable microsoft visual basic extensibility 5.3 in references for whichever workbook you run this code out of.

Sub HighlightRow()
' Highlight the entire row of the cell selected

On Error GoTo NextSection
ActiveWorkbook.VBProject.References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
        
NextSection:
Dim CodeMod As VBIDE.CodeModule
Set cmod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.ActiveSheet.CodeName).CodeModule
 
' Remove the Conditional formatting and "screen updating" code from the active sheet (toggle off)
    For i = 1 To Cells.FormatConditions.Count    ' loops through all conditional formatting rules
        If Cells.FormatConditions(i).Formula1 = "=CELL(""row"")=ROW()" Then
            Cells.FormatConditions(i).Delete
            With cmod
                On Error GoTo ExitS
                .DeleteLines 1, [5]
            End With
ExitS:
MsgBox ("uncheck ""Trust access to the VBA project object model""")
            Exit Sub
            On Error Resume Next
        End If
    Next i
 
' if the above rule is not found, inserts rule and "screen updating" code into activesheet (toggle on)
    Cells.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CELL(""row"")=ROW()"
    Cells.FormatConditions(Cells.FormatConditions.Count).SetFirstPriority
    With Cells.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Cells.FormatConditions(1).StopIfTrue = False
    
    With cmod
        .InsertLines Line:=.CreateEventProc("SelectionChange", "Worksheet") + 1, _
                     String:=vbCrLf & "    application.screenupdating = True"
    End With
    Application.VBE.MainWindow.Visible = False

End Sub