Why is this code saying Method Range of Worksheet failed

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
631
Office Version
  1. 2019
Platform
  1. Windows
This code says "Method Range of Worksheet failed" on this line
VBA Code:
 colorAbove ws.Range("A13:Q & LastRow").

Can`t understand why???

VBA Code:
Private Sub Add_Break_Lines_Change()

    Dim cmb As ComboBox
    Dim ws As Worksheet
    Dim LastRow As Long


    Set ws = ThisWorkbook.Worksheets("Job Card Master")
    Set cmb = Me.Add_Break_Lines
    
    cmb.AddItem "Break Lines 1 Page Job Card"
    cmb.AddItem "Break Lines 2 Page Job Card"
    cmb.AddItem "Break Lines 3 Page Job Card"
    cmb.AddItem "Break Lines 4 Page Job Card"
    cmb.AddItem "Break Lines 5 Page Job Card"
    
    LastRow = ws.Cells(Rows.Count, 3).End(xlUp).row
    MsgBox LastRow

    
    Select Case cmb.Value
    
          Case ("Break Lines 1 Page Job Card")
                        colorAbove ws.Range("A13:Q & LastRow")
            
          Case ("Break Lines 2 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q & LastRow")
                        
          Case ("Break Lines 3 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q & LastRow")
                        
           Case ("Break Lines 4 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q183")
                        colorAbove ws.Range("A188:Q & LastRow")
                        
           Case ("Break Lines 5 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q183")
                        colorAbove ws.Range("A188:Q244")
                        colorAbove ws.Range("A249:Q & LastRow")
    End Select

End Sub
Sub colorAbove(rng As Range)
    
    Dim brg As Range
    Dim rrg As Range
    Dim EmptyRowNum As Long
    Dim i As Long
    
    For i = 1 To rng.Rows.Count
        Set rrg = rng.Rows(i)
        If WorksheetFunction.CountA(rrg) = 0 Then
            EmptyRowNum = EmptyRowNum + 1
        End If
        If EmptyRowNum = 2 Then
            EmptyRowNum = 0
            If brg Is Nothing Then
                Set brg = rrg
            Else
                Set brg = Union(brg, rrg)
            End If
        End If
    Next i
    
    If Not brg Is Nothing Then
        brg.Interior.ColorIndex = 6
    End If

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
NB untested; test on a copy of your work, first.

Try:
VBA Code:
colorAbove ws.Range("A13:Q" & LastRow)

... and duplicate for all the other instances, of course.
 
Last edited:
Upvote 0
colorAbove is a sub so
call colorAbove (ws.Range("A66:Q" & LastRow))

Or You make it as function then
colorAbove (ws.Range("A66:Q" & LastRow))
 
Upvote 0
Cause we can not test so witch one was the solution for you? If you please
 
Upvote 0
Whether it is a sub or function does not affect how you call it. For either one, you could use Call with parentheses, or don't use Call and don't use parentheses.
 
Upvote 0
Solution
Pleasure, and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top