vba - Trying to fill a colored line above filled in cells

Question

My code is to fill color into rows above filled in rows. There is 2 rows above the text it's the bottom row of the 2 needs to fill in color? They should be linked to the ranges shown in the Sub above the Function What I can't really understand that it seemed to work the other day but now it`s stopped working? I think the sub is right just the Function seems to be failing?? Not sure how to link the 2 together?

   Private Sub Fill_Color_Click()
   Dim Com As ComboBox
   Dim ws As Worksheet

   Set Com = Me.Fill_Color

   With ws
   
    Select Case Com.Value
        Case ("Fill Color 1 Page Job Card")
            Color .Range("A13:Q61")

        Case ("Fill Color 2 Page Job Card")
            Color .Range("A13:Q61")
            Color .Range("A66:Q120")
   
        Case ("Fill Color 3 Page Job Card")
             Color .Range("A13:Q61")
            Color .Range("A66:Q122")
            Color .Range("A127:Q178")
            
        Case ("Fill Color 4 Page Job Card")
            Color .Range("A13:Q61")
            Color .Range("A66:Q122")
            Color .Range("A127:Q183")
            Color .Range("A188:Q244")

        Case ("Fill Color 5 Page Job Card")
            Color .Range("A13:Q61")
            Color .Range("A66:Q122")
            Color .Range("A127:Q183")
            Color .Range("A188:Q244")
            Color .Range("A249:Q299")
    End Select
    
End With


End Sub

Function Color(rng As Range)

Dim row As Range
Dim sheet As Worksheet
Set ws = ThisWorkbook.Sheets("Job Card Master")
Dim EmptyRowNum As Integer

For i = 1 To rng.Rows.Count

    Set row = rng.Rows(i)
    If WorksheetFunction.CountA(row) = 0 Then
        EmptyRowNum = EmptyRowNum + 1
    End If
    If EmptyRowNum = 2 Then
        EmptyRowNum = 0
        row.Interior.ColorIndex = 4
    End If

Next i

End Function
       

question from:https://stackoverflow.com/questions/65881960/trying-to-fill-a-colored-line-above-filled-in-cells

Answer 1

You have a naming error, sheet vs ws: Dim sheet as Worksheet in the Color() function but then you set ws = ....

Anyway, it is misplaced, you need to move it to Fill_Color_Click():

Private Sub Fill_Color_Click()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Job Card Master")

so that the references to the ranges make sense.