conditional formating using dates

J

jorgie

I have data that is entered from a report that is done periodically and don’t
want users to use the spreadsheet after a specific date (this date is entered
into a header). So I want to be able to look at the current date and compare
it to the header date and if the current date exceeds the header date then
the spreadsheet becomes unusable.
 
M

Max

jorgie said:
I have data that is entered from a report that is done periodically and don’t
want users to use the spreadsheet after a specific date (this date is entered
into a header). So I want to be able to look at the current date and compare
it to the header date and if the current date exceeds the header date then
the spreadsheet becomes unusable.
Click to expand...

No answer, just some thoughts. Don't think CF* can render this:
the spreadsheet becomes unusable
Click to expand...
*going by your subject line <g>

Hang around for better insights ..
 
N

Naveen

Try this in VBA (Tools->Macro->Visual Basic Editor then select required sheet)

Assuming your date for validation is in A1.

Copy the following code.

=========================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") >= Date Then
With Cells.Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Sheet Expired"
.InputMessage = ""
.ErrorMessage = "Sheet Expired"
.ShowInput = True
.ShowError = True
End With
End If
End Sub
=========================================


Please rate me.
 
B

Biff

If you delete the date in cell A1 the code still "locks" the sheet. Also,
how does the owner of the file "unlock" it?

Biff
 
Top