Macro to show row number and sheet name

[LalitPandey87]

Here you go:

Open your excel file and press Alt + F11 then Press Ctrl + R and your left hand side double click on Thisworkbook and paste below code in right side window.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    Call lmp_Test


End Sub


Sub lmp_Test()


    Dim varData()           As Variant
    Dim rngToCheck          As Range
    Dim strShtName          As String
    Dim lngLoop             As Long
    Dim strMsg              As String
    
    'Change accordingly
    Const strColToCheck     As String = "A1" 'Is the starting cell of the column which contains account numbers
    Const lngColToMatch     As String = 3 'Is the column no in which i need to check 0 value for the same row account number.
    
    With ActiveSheet
        strShtName = .Name
        Set rngToCheck = .Range(strColToCheck)
        If .Cells(.Rows.Count, rngToCheck.Column).End(xlUp).Row >= rngToCheck.Row Then
            Set rngToCheck = rngToCheck.Resize(.Cells(.Rows.Count, rngToCheck.Column).End(xlUp).Row).Resize(, lngColToMatch)
            varData = rngToCheck.Value
            strMsg = vbNullString
            For lngLoop = LBound(varData) To UBound(varData)
                If LenB(Trim(varData(lngLoop, 1))) > 0 Then
                    If LenB(Trim(varData(lngLoop, lngColToMatch))) = 0 Then
                        strMsg = strMsg & IIf(LenB(Trim(strMsg)) > 0, vbLf, vbNullString) & strShtName & " " & rngToCheck.Resize(1, 1).Offset(lngLoop - 1, lngColToMatch - 1).Address(0, 0)
                    End If
                End If
            Next lngLoop
        End If
        If LenB(Trim(strMsg)) > 0 Then
            MsgBox strMsg
        End If
        
    End With
    
    


End Sub