MS excel macro auto count function, result display in window pop up

Good day to all, currently I'm still facing problem of my boss task, to create a MS excel macro.

Problem facing is still the same:

auto count outdated data and show in the message box when user open worksheet.

On previous question I'm already asked some solution and combined those suggestion codding with my original codding, but the result also the same, the message pop up still as 0 even though there is outdated contract of employees.

Below are the combination of your suggestion and my original codding...please have a look.

Below are the combination of your suggestion and my original codding...please have a look and feel free to comment to let me know what's going wrong thx. i need it ASAP..

Sub Worksheet_Activate()

Dim startCell As Integer, endCell As Integer
Dim column As Integer
Dim CountCells As Integer
Dim x As Integer

With Worksheets("Sheet1")

lastrow = Range("L1048576").End(xlUp).Row



For i = 4 To lastrow

    If Range("L" & i).Value <> "" And Now <> "" Then

       If Range("L" & i).Value <= Now Then

           Range("L" & i).Font.ColorIndex = 3

        End If
    End If
Next i

    column = 12 'Column L

    startCell = 4
    endCell = xlUp

    CountCells = 0



    For x = startCell To endCell Step 1

    If Cells(x, column).Interior.ColorIndex = 3 Then

        CountCells = CountCells + 1 


    End If
Next x

    MsgBox CountCells & " expiring"

End With
End Sub

Solution

why not use the same lastrow instead of creating endCell this would ensure that the code is being run on the same range of values.

you could also change the endCell to

endCell = Range("L1048576").End(xlUp).Row

I don't think xlUp by itself would work.

edit:

Sub Worksheet_Activate()

Dim startCell As Integer, endCell As Integer
Dim column As Integer
Dim CountCells As Integer
Dim x As Integer
Dim lastrow As Integer
Dim i As Integer



With Worksheets("Sheet1")

lastrow = Range("L1048576").End(xlUp).Row



For i = 4 To lastrow

    If Range("L" & i).Value <> "" And Now <> "" Then

        If Range("L" & i).Value <= Now Then

            Range("L" & i).Interior.ColorIndex = 3

        End If
    End If
Next i

column = 12 'Column L

startCell = 4

CountCells = 0


For x = startCell To lastrow Step 1

    If Cells(x, column).Interior.ColorIndex = 3 Then

        CountCells = CountCells + 1

    End If

Next x

MsgBox CountCells & " expiring"

End With
End Sub