Macro troubles

[Peter Taylor]

Dear Members
You can personally reply to (e-mail address removed)
Whilst this seems to work for the current date can I trap the previous
dates as I have tried below (with no success)? Because it will not be
every day that I would be opening this worksheet, so when I do open up
this worksheet it will format, not only the current date, but also any
previous days as well?

Private Sub Workbook_Open()
For Each cell_in_loop In Range("c349:c900")
If Format(cell_in_loop.Value, "dd/mm/yy") = Format(Now,
"dd/mm/yy") Or Format(cell_in_loop.Value, "dd/mm/yy") < Format(Now,
"dd/mm/yy") Then
With cell_in_loop.Offset(0, 0)
.Interior.ColorIndex = 1
.Interior.Pattern = xlSolid
.Font.ColorIndex = 2
Exit Sub
End With
End If
Next
End Sub

Cheers Peter Taylor

 

[Nick Hodge]

Peter

Attacking it slightly differently. I would store a value for the last
opening. (I've put it in A1, you can change that). You could also read the
last modified property

Hope I've understood the need (I also commented out the Offset as it does
nothing)

Private Sub Workbook_Open()
Dim cell_in_loop As Range
Dim dCurr As Date
dCurr = Format(Date, "dd/mm/yy")
Range("A1").Value = Date
For Each cell_in_loop In Range("c349:c900")
If Format(cell_in_loop.Value, "dd/mm/yy") = _
Format(Now, "dd/mm/yy") Or Format(cell_in_loop.Value, "dd/mm/yy") _
< Range("A1").Value Then
With cell_in_loop 'No need for this.Offset(0, 0)
.Interior.ColorIndex = 1
.Interior.Pattern = xlSolid
.Font.ColorIndex = 2
Exit Sub
End With
End If
Next
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)