Look at last 7 rows in worksheets, put results to sheet 1

I have a macro that will output a negative value if something is wrong with a certain row. What I want to do now is make a macro that will scan sheet 2 thru the end for the last 7 rows with data, and count negative values. If 4 of the 7 have negative values, it goes into Sheet 1 and puts an x next to the sheet name.

Logic:
hit button on sheet 1 -> go to Sheet 2 - count negative values in last 7 rows on column K (11) -> if more than 4, go to first sheet to the sheet name (column J) -> go right 2 -> put an X -> go to sheet 3

I tried my hand at writing some of the code, but I'm still very new at excel...

'loop thru all the worksheets, selecting the last 7 values in column J and defining that as a range. in the code below, I try to do stuff with the range but I get very lost there.

Sub count()
Dim wsheet As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
For Each wsheet In wb.Worksheets
    With wsheet
        If wsheet.Name <> "Sheet1" Then
            wsheet.Activate
            With ActiveSheet
                Set rng = Range("J65536").End(xlUp).Offset(-7, 0)
            End With
        End If
    End With
Next

End Sub

'not sure how to implement this part about writing to the first sheet

IF(COUNTIF(rng,"<0")>4,......,.....)

Thanks, I'm really terrible at code...

xmmx,

Give this a try:

Sub count()
    
    Dim wsIndex As Integer, rngLast7 As Range, rngDest As Range
    For wsIndex = 2 To ActiveWorkbook.Sheets.count
        With Sheets(wsIndex)
            Set rngLast7 = .Cells(Rows.count, "K").End(xlUp)
            Set rngLast7 = .Range(rngLast7, rngLast7.Offset(-6, 0))
        End With
        If WorksheetFunction.CountIf(rngLast7, "<0") > 4 Then
            Sheets(1).[J:J].Find(Sheets(wsIndex).Name).Offset(0, 2).Value = "X"
        End If
    Next
    
End Sub

Notes:
-If, in column J of sheet1, the worksheet name is not found, the macro will return an error.

Hope that helps,
~tigeravatar

Thanks so much, this works exactly like I imagined!