Placing a reference in another worksheet after an alert has been activated in another work

[clabhart]

I have created an alert for if a condition is met in one worksheet. I want to create a reference to this alert in another workbook with the date the alert was generated. Here is a sample workbook with the code for the alert and the way I tried to create the reference. Any help is greatly appreciated.SAMPLE.xlsm

[clabhart]

Here is an updated version of the code I'm trying to use

Sub test()
Dim rng As Range
Dim rng2 As Range
On Error Resume Next
Dim a As Integer
Set rng2 = Sheets("Sheet2").Range("A1")
For Each rng In Range("Offset( B1, 0, 0, COUNTA(A:A), 1)")
    If rng.Value = Evaluate("TRUE") Or rng.Value = "PENDING" And Cells(rng.Row, "D") = "Y" And Cells(rng.Row, "E") = "" Then
        Cells(rng.Row, "E").Value = "RESOLVED"
        rng.Value = "PENDING"
        MsgBox "Alert for " & rng.Offset(0, -1).Value & ".", vbOKCancel, "Alert"
        rng2.Value = Cells(rng.Row, "A")
        rng2.Offset(0, 1).Value = Sheet1.Range("D7") & " ALERT"
        rng2.Offset(0, 2).Value = Evaluate("TODAY()")
    ElseIf Cells(rng.Row, "D") = "N" And Cells(rng.Row, "E") = "RESOLVED" Then
        Cells(rng.Row, "E").Value = ""
        rng.Value = "TRUE"
        End If
Next
On Error Resume Next
End Sub

[clabhart]

I found the solution here is the code for any one who is curious

Sub test()
Dim rng As Range
Dim rng2 As Range
On Error Resume Next
Dim a As Long
Set rng2 = Sheets("Sheet2").Range("A1")
For Each rng In Range("Offset( B1, 0, 0, COUNTA(A:A), 1)")
    If rng.Value = Evaluate("TRUE") And Cells(rng.Row, "D") = "Y" And Cells(rng.Row, "E") = "" Then
        Cells(rng.Row, "E").Value = "RESOLVED"
        rng.Value = "PENDING"
        MsgBox "Alert for " & rng.Offset(0, -1).Value & ".", vbOKCancel, "Alert"
        With Sheets("Sheet2")
            a = .Range("A" & .Rows.Count).End(xlUp).Row
            rng2.Offset(0 + a, 0).Value = Cells(rng.Row, "A")
            rng2.Offset(0 + a, 1).Value = Sheet1.Range("D7") & " ALERT"
            rng2.Offset(0 + a, 2).Value = Evaluate("TODAY()")
            End With
    ElseIf Cells(rng.Row, "D") = "N" And Cells(rng.Row, "E") = "RESOLVED" Then
        Cells(rng.Row, "E").Value = ""
        rng.Value = "TRUE"
    ElseIf rng.Value = "PENDING" And Cells(rng.Row, "D") = "Y" And Cells(rng.Row, "E") = "" Then
        Cells(rng.Row, "E").Value = "RESOLVED"
        rng.Value = "PENDING"
        MsgBox "Alert for " & rng.Offset(0, -1).Value & ".", vbOKCancel, "Alert"
        With Sheets("Sheet2")
            a = .Range("A" & .Rows.Count).End(xlUp).Row
            rng2.Offset(0 + a, 0).Value = Cells(rng.Row, "A")
            rng2.Offset(0 + a, 1).Value = Sheet1.Range("D7") & " ALERT"
            rng2.Offset(0 + a, 2).Value = Evaluate("TODAY()")
            End With
        End If
Next
On Error Resume Next
End Sub

Also including the sample workbook which I used to create the code
SAMPLE.xlsm