Hi,

I have the below code, i need to change the subject line to pick up columns in sheet 1 based on the dates.
Can someone help me to change this code and tell me what do i use instead.

Sub CheckForExpiryDates()

Dim Cell As Range
Dim ExpiryDate As Date
Dim Mail_Msg As String
Dim Mail_Subj As String
Dim Rng As Range
Dim RngEnd As Range

Mail_Subj = "Check the file"
Mail_Msg = "Hi, Please complete activity in subject line" & vbCrLf _
& "and comfirm once done."

Set Rng = Worksheets("Sheet1").Range("A2")
Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))

For Each Cell In Rng.Cells
ExpiryDate = Cell.Offset(0, 2)
If DateDiff("d", Now(), ExpiryDate) <= 1 And Cell.Offset(0, 3) = "" Then
SendEmail Cell.Offset(0, 1), Mail_Subj, Mail_Msg
Cell.Offset(0, 3) = Now()
End If
Next Cell

End Sub