How to solve run-time error in VBA Excel?

0
votes

When I run code in VBA Excel. I get run-time error '1004' Method 'Range' of object'_ Worksheet failed. Here is the code.

Private Sub Worksheet_Change(ByVal Target As Range)

With Range("A" & firstTickerRow & ":A" & Rows.Count).Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

With Range("A" & firstTickerRow - 1 & ":A" & Rows.Count)
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With

lastRowColA = Cells(Rows.Count, "a").End(xlUp).Row

With Range("A" & firstTickerRow - 1 & ":A" & lastRowColA)
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
End With

With Range("A" & firstTickerRow & ":A" & lastRowColA).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With

End Sub

1
excelvba
what is firstTickerRow – Scott Craner
Which line is throwing the error? – RBarryYoung
First row @RBarryYoung - 'Private Sub Worksheet_Change(ByVal Target As Range)' – Zeeshan Hafeez
You do not assign a value to firstTickerRow and therefore it is 0 and there is no 0 row. – Scott Craner
Right, as @ScottCraner says, "A0:A99" is an invalid range address because there's no row 0. Likewise, later on addresses like "A-1:A88" are also invalid. You need to either make sure the constructed ranges are valid, or else use error-handling to catch and skip over bad references. – RBarryYoung

1 Answers

0
votes

Formatting in a Worksheet Change Event

  • You usually want to restrict this to a certain range like in this case to a column range (A). This is done in the line containing Intersect.
  • Adjust the two constants.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const tCol As String = "A"
    Const ftRow As Long = 2
    
    If Intersect(Target, Columns(tCol)) Is Nothing Then Exit Sub
    If ftRow < 2 Or ftRow > Rows.Count Then Exit Sub
    
    With Range(tCol & ftRow & ":" & tCol & Rows.Count).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
        
    With Range(tCol & ftRow - 1 & ":" & tCol & Rows.Count)
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    
    Dim lRow As Long: lRow = Cells(Rows.Count, "A").End(xlUp).Row
    If lRow < ftRow Then Exit Sub
    
    With Range(tCol & ftRow - 1 & ":" & tCol & lRow)
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlMedium
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlMedium
    End With
    
    With Range(tCol & ftRow & ":" & tCol & lRow).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

End Sub