Try something like

Dim S As String
S = InputBox("Enter A Date")
If S = "" Then
Exit Sub
End If
' your code
If cell.Value <= DateValue(S) Then
' your code


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"SMac" wrote in message
...
I have the following code:

Sub ShadeCells()

Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue("2/08/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 16
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("G1").Value = dblSum
.Font.Bold = True
.NumberFormat = "$#,##0.00"

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Works great, now instead of going in and changing the date
every 2 weeks
(this is how often I use this code) I would like an InputBox
come up and ask
for the date to use and then use that entry in the criteria
section:

If cell.Value <= DateValue("2/08/2005") Then

Any suggestions would be great, Thanks!