Executing the VBA code on opening the excel instea

Question

I have a VBA code for a Rota Sheet that is activated on change of any value in the row.

I want the code to be activated upon opening the excel.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Range("B2:V11")

If Not Intersect(Target, rng) Is Nothing Then

    'scan each row (month)
    Dim countRow As Long

    Dim i As Long
    For i = 1 To rng.Rows.count
        If Not Intersect(Target, rng.Rows(i)) Is Nothing Then
            If WorksheetFunction.CountIf(rng.Rows(i), "V") > 0 Then
                countRow = 0

                Dim cel As Range
                For Each cel In rng.Rows(i).Cells
                    If cel.Value2 = "V" Then
                        countRow = countRow + 1
                        VacationChange cel, countRow
                    Else
                        VacationChange cel, 0
                    End If
                Next cel
            End If
        End If
    Next i

    'scan each column (day)
    Dim j As Long
    For j = 1 To rng.Columns.count
        If Not Intersect(Target, rng.Columns(j)) Is Nothing Then
            If WorksheetFunction.CountIf(rng.Columns(j), "V") > 5 Then
                VacationChange rng.Columns(j).Cells(0, 1), 6
            Else
                VacationChange rng.Columns(j).Cells(0, 1), 0
            End If
        End If
    Next j

End If
End Sub

Private Function VacationChange(ByVal rng As Range, ByVal count As Long)

With rng.Interior
    Select Case count
        Case 0
            'clear cell colors
            .Pattern = xlNone
            .Color = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        Case 1 To 3
            'blue
            .Pattern = xlSolid
            .Color = 15773696
            .TintAndShade = 0
            .PatternTintAndShade = 0
        Case 4 To 5
            'yellow
            .Pattern = xlSolid
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        Case Else
            'red
            .Pattern = xlSolid
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
    End Select
End With

End Function

I spent efforts by trying: 1. Using below code in Workbook: which is throwing 424 error

Private Sub Workbook_Open()
Sheet1.Activate
Call Worksheet_Change(Target)
End Sub

2. Pasting the entire code under Workbook_Open() function which is not working

Can anyone suggest what i am missing in the code ?

Sample Output image is attached enter image description here

Answer 1

The problem is that Target is an undeclared Variant in your Workbook_Open implementation. That means when it gets passed as a parameter that needs to be a Range, the implicit cast fails and results in an error 424 (Object required).

If you want to "simulate" every cell in your target range changing, you can simply loop over B2:V11 and pass it each individual cell (untested with your data, but should give the gist):

Private Sub Workbook_Open()
    Sheet1.Activate
    Dim cell As Range
    For Each cell In Sheet1.Range("B2:V11")
        'Worksheet_Change needs to be Public
        Sheet1.Worksheet_Change cell
    Next
End Sub

Note that this is by no means the ideal solution to what you are trying to do and is a sign that you need to refactor your code a little bit to extract the functionality that you currently have in Worksheet_Change into a free-standing procedure. If you need to run the same code from the Worksheet_Change handler, you can call that procedure.