Code to hide rows

[dezmond]

Hi Guys wonder if anyone can help me basically i have a code in my worksheet that looks like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("S1:S40")
If cell.Value = 1 Then
cell.EntireRow.Hidden = True
ElseIf cell.Value = 0 Then
cell.EntireRow.Hidden = False
End If
Next
Application.ScreenUpdating = True
End Sub

However its not a worksheet that I have to put data its just to look at (its a work shift pattern based on dates) as each work day finishes I have used conditional formatting to hide the date and hours worked however once it gets to a Sunday i want the whole row to disappear therby whenever i look at the worksheet the current week is always at the top of the sheet with days that have already passed that week just blanked out. using a formula that calculates that if a given sunday is less than todays date it obviously gives me a true or false i have then used a countif function and if it equals true obviously gives a value of 1 which is what ive used in column "s" to base the hidden rows on. the problem is when I open the worksheet the rows dont disappear until i acutally put something in any other cell which i dont want to do because as i said its only a "look at" worksheet. I hope you can understand what I am trying to do and this hasnt been too long winded

thanks