How to auto delete cells after a specific time period

[rizwanulhasan]

Hi All,

I have a sheet as below where when cell C4 is set to Do Not Disturb, B4 switches to Not Available. After 29 seconds, cell C4 deletes automatically thus switching B4 back to Available.

Book1.xlsm
	A	B	C	D
1
2
3	Name	Availability	DND Status	Date
4	A	Available		02-01-2022 12:00 PM
5	B	Available
6	C	Available
7	D	Available
Sheet1

Cell Formulas
Range	Formula
B4:B7	B4	=IF(C4="Do Not Disturb","Not Available","Available")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:C7	Cell Value	="Do Not Disturb"	text	NO
B4:B7	Expression	=B4="Available"	text	NO
B4:B7	Expression	=B4="Not Available"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B4:B7	List	Available, Not Available
C4:C7	List	Do Not Disturb

This is the VBA used and a sheet "Time Log" has been created.

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("C4:C100")

If Now > stampcell.Value2 + TimeValue("00:00:29") 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("C4:C100") ''' 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

However, what I want is that once C4 is set to Do Not Disturb, instead of deleting it automatically after 29 seconds, it should ask to enter the date and time in the adjacent CELL D4.

For ex. D4 = 02-01-2022 12:00 PM. Now the cell C4 should remain as Do Not Disturb until the date and time mentioned in D4. Post this, it should delete itself thus switching B4 back to Available.

Can someone please help with such kind of VBA.

Thanks in advance.

Note: same thread also posted on excelforum.

How to auto delete cells after a specific time period

Hi All, I have a sheet attached where when cell C4 is set to Do Not Disturb, B4 switches to Not Available. After 29 seconds, cell C4 deletes automatically thus switching B4 back to Available. However, what I want is that once C4 is set to Do Not Disturb, instead of deleting it...

www.excelforum.com