Reset/Clear data in a column

[da024]

I have a very basic excel sheet that I share on a server with other team members to track if they performed certain tasks, one of the tasks has to be completed monthly. This column (E6 to E68) called “Verified” has basic conditional formatting in it as well so if a user enters a simple YES it turns green. What I was trying to do is have that range of cells reset to either “NO” or clear it leaving the conditional formatting at the beginning of each month automatically so I don’t have to do it all the time. Anyone know a simple way to achieve this? Thanks in advance!

 

[Joe4]

Welcome to the Board!

Try this:

    Range("E6:E68").ClearContents

 

[da024]

How do you get the to run on the 1st day of each month?

 

[Joe4]

D

How do you get the to run on the 1st day of each month?

Do you mean automatically, or is someone going to be running the code manually?
If you want it to happen automatically, does someone open the file everyday?
Or will you have a Scheduler open it?

 

[da024]

Good question, I would like it to be as hands off as possible, I think it would work fine if it was to run when anyone first opens the workbook, would it catch up if the file was not opened for several days?

 

[Joe4]

If you put VBA code in the Workbook_Open event procedure, it will run automatically when the file is opened, as long as Macros/VBA are enabled.

The way that I would do this is to pick some unused cell to track the last time it was updated. Then every time the file is opened, it will compare the current month to the month of the date stored in that cell. If it is different, it will run the code to clear the range, update the last updated cell, and save itself.

Here is what that code would look like:

Private Sub Workbook_Open()
    
'   Check to see if month if last update cell (Z1 in Sheet1), doesn't match current month
    If Month(Sheets("Sheet1").Range("Z1")) <> Month(Date) Then
'       If it doesn't, clear range, update last update cell, and save file
        Sheets("Sheet1").Range("E6:E68").ClearContents
        Sheets("Sheet1").Range("Z1") = Date
        ActiveWorkbook.Save
    End If

End Sub

This code MUST be placed in the "ThisWorkbook" module in the VB Editor in order for it to run automatically. If you place it any other module, it will not work.

Note that I chose cell Z1 on Sheet1 to store the last update date. Be sure to change those sheet and cell addresses in the code to suit your needs.

 

[da024]

Thanks!

 

[Joe4]

You are welcome.

 

[da024]

You mentioned setting a cell such as Z1 to be the "last update", could I simply use the now() function to always have the lastest time instead?

 

[Joe4]

You mentioned setting a cell such as Z1 to be the "last update", could I simply use the now() function to always have the lastest time instead?

No, the NOW() function is a dynamic formula that always returns the current date and time.
You want to capture the last date the update was run, so it needs to be a hard-coded value, not a formula that will always show you current date/time.

This line of code is setting Z1 equal to the current date/time when it is run, but hard-coding it, so it doesn't change (until the code is run again and the conditions are met):

Sheets("Sheet1").Range("Z1") = Date

Note that the Date function in VBA is similar to the TODAY() function in Excel (which returns the current Date, without a time component).