Activating only the last 30 days in Excel Calendar?

[yaso1]

I'm trying to create a data collection tool in a research study that will only provide the last 30 calendar days from the date it is opened. I'm using Excel Calendar, and I put the information for the date ranges in two cells on the side of the calendar, where the 30-day period start date is L4 (4,12) and end date is in L3 (3,12). I want to lock and black-out the calendar days that are not within that 30-day range to minimize confusion and error.


I pasted what I wrote into VBA so far. I practiced with 4 consecutive calendar days (all in row 6), where 2 of them should have been blacked out because they were outside of the 30-day range.


(Note: each calendar cell has two blank cells below it for data entry, which is why I want to lock and blackout 3 cells at a time for each calendar day.)


-----------------------------------------------

If ActiveSheet.Cells(6, 2).DateValue <= Cells(3, 12).DateValue And ActiveSheet.Cells(6, 2).DateValue >= Cells(4, 12).DateValue Then
ActiveSheet.Range(Cells(6, 2), Cells(7, 2), Cells(8, 2)).Locked = False
Else: ActiveSheet.Range(Cells(6, 2), Cells(7, 2), Cells(8, 2)).Locked = True
End If


If ActiveSheet.Cells(6, 2).DateValue <= Cells(3, 12).DateValue And ActiveSheet.Cells(6, 2).DateValue >= Cells(4, 12).DateValue Then
ActiveSheet.Range(Cells(6, 2), Cells(7, 2), Cells(8, 2)).Selection.Interior.Color = RGB(0, 0, 0)
Else: ActiveSheet.Range(Cells(6, 2), Cells(7, 2), Cells(8, 2)).Selection.Interior.Color = RGB(0, 0, 0)
End If

I also tried this:

If ActiveSheet.Cells(6, 3).DateValue <= Cells(3, 12).DateValue And ActiveSheet.Cells(6, 3).DateValue >= Cells(4, 12).DateValue Then
ActiveSheet.Range(Cells(6, 3), Cells(4, 3), Cells(5, 3)).Locked = False
Else: Locked = True
Selection.Interior.Color = RGB(0, 0, 0)
End If


If ActiveSheet.Cells(6, 4).DateValue <= Cells(3, 12).DateValue And ActiveSheet.Cells(6, 4).DateValue >= Cells(4, 12).DateValue Then
ActiveSheet.Range(Cells(6, 4), Cells(6, 4), Cells(6, 4)).Locked = False
Else: Locked = True
Selection.Interior.Color = RGB(0, 0, 0)
End If


If ActiveSheet.Cells(6, 5).DateValue <= Cells(3, 12).DateValue And ActiveSheet.Cells(6, 5).DateValue >= Cells(4, 12).DateValue Then
ActiveSheet.Range(Cells(6, 5), Cells(6, 5), Cells(6, 5)).Locked = False
Else: Locked = True
Selection.Interior.Color = RGB(0, 0, 0)
End If


End Sub

------------------------


None of this has worked. I'm not sure if I'm getting the DateValue Function wrong, or if I'm missing something else. I was hoping to eventually use a Loop function once I got this step figured out, but I haven't even gotten the first part down...


Any help would be much appreciated!!!

 

[Peter_SSs]

Welcome to the MrExcel board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: Activating only the last 30 days in Excel Calendar?

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[yaso1]

Oops, I apologize for doing that. I'm new to the world of Excel VBA and I'm figuring this stuff out as I go, so I appreciate your feedback . I read the link you provided and I will edit my post with the cross-post URLs. Thanks!

 

[yaso1]

This post is cross-posted at the following sites:

Activating only the last 30 days in Excel Calendar?

Activating Only the Last 30 Days in Excel Calendar? - Toolbox for IT Groups

Initially, I did not understand that cross-posting within a short timespan is not recommended. I'm under a bit of a time crunch and wanted to figure this out as efficiently as possible. Now I understand that sharing the cross-post URLs is part of that process. Thanks!