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

Question

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

score 0 · Answer 1 · answered Apr 05 '16 at 06:35

0

When using With...End With, all objects which belong to the With clause should be prepended with . (period)

E.g.

With Worksheets("Sheet1")
    lastrow = Range("L1048576").End(xlUp).Row

should be

With Worksheets("Sheet1")
    lastrow = .Range("L1048576").End(xlUp).Row

Make the fixes and see if that helps. If still not working, update your question with your current code.

answered Apr 05 '16 at 06:35

Tim Williams

154,628
8
97
125

@Tim_Williams sorry dude, it is not working, due to the comment box had limit space, i cant copy paste the complete codding here. – Han Apr 05 '16 at 07:33

ballsy26 · Accepted Answer · 2016-04-05T08:24:20.550

0

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

edited Apr 05 '16 at 08:24

answered Apr 05 '16 at 06:45

ballsy26

145
1
11

sorry bro, it's not working, I had changed to only use 1 lastrow or use both lastrow & endcell with the same Range("L1048576").End(xlUp).Row it also not working. is that possible for you to write down a complete codding here rather than just a part of it. – Han Apr 05 '16 at 07:38
i added the code to my answer, something else i noticed is that initially you are changing the `font.colorindex` but later you are looking for `interior.colorindex`. i altered it to change the `interior` – ballsy26 Apr 05 '16 at 08:25
yeah! I noticed it too, that's why my previous codding not running...anyway thx! – Han Apr 05 '16 at 08:50

score 0 · Answer 3 · answered Apr 05 '16 at 08:26

Problems had been solved, below are the correct / usable codding. Thx to all of you, only I able to keep testing and modifying the codding.

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

CountCells = 0

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

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

                   CountCells = CountCells + 1

            End If
        End If
    End If
Next i

   MsgBox CountCells & " expiring"

End With
End Sub

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

3 Answers3