How to delete a cell after a certain period of time?

Hi everyone,

I am looking to see if it is possible to write automate something I am doing at work. I am looking to have a code that deletes a cell's information after 6 months if the color of the text has been changed to red.

Specifically, I have a list of phone extensions with names. If a name is changed to red, after 6 months, the name can be deleted rather than having to manually go in and see when it was changed to red from a comment posted by whoever changed the color.

I am not exactly sure how to write it but this is what I have so far (found on another forum). I have 2 spreadsheets, one named Data with all the data and one named Time Log that has the date and time the cell was updated. Any advice would be great!! I don't have a lot of experience writing code but I am trying to learn more.

---

Private Sub Worksheet_Activate()

Dim stampcell As Range

Dim TLSh As Worksheet


Set TLSh = Worksheets("Time Log")


Application.ScreenUpdating = False

''' Check if pass the 24h period to delete content

For Each stampcell In TLSh.Range("B2:B1000")

If Now > stampcell.Value2 + TimeValue("23:59:59") Then

Me.Range(stampcell.Address).ClearContents

stampcell.ClearContents

End If

Next stampcell

Application.ScreenUpdating = True


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range

Dim stampcell As Range

Dim TLSh As Worksheet


Application.EnableEvents = False


Set TLSh = Worksheets("Time Log") ''' Time stamp sheet

Set myRng = Range("B2:B1000") ''' Your targeted range



If Not Intersect(Target, myRng) Is Nothing Then '''Time-stamping

TLSh.Range(Target.Address).Value2 = Now

TLSh.Range(Target.Address).NumberFormat = "MM/DD/YYYY hh:mm:ss"

End If



Application.EnableEvents = True


End Sub

Maybe something like this?

Please Login or Register  to view this content.

So, after breaking the golden rule of this forum by not using code tags (which the moderators MISSED by the way...tut tut), you now have had a few days (at least) to analyse the responses.

Are you just going to cut and run or are you going to come back, tell us how great we are and give us LOADS and LOADS of rep points?

Rep points are the currency here ya know and we all have families to feed! LOL.