Have you thought of using Format|Conditional formatting?

You get up to 3 formats (plus the normal one)?

It might be simpler and less work for excel to do.


Andrew Clark wrote:

Hello,

I would like to write a function to change the background color of a
range based on the date in the first column. I am not very familiar with
VBA's objects though. This is what I had:

Private Sub Worksheet_Activate()
Dim y As Integer, strRange As String
' Ideally, I would like to go to the last row, but 200 will be
' sufficient for my purposes
For y = 0 To 200
If Month(Now) = Month(Worksheets(1).Cells(y, 0)) And _
Year(Now) = Year(Worksheets(1).Cells(y, 0)) Then
' Highlight this row (columns A to L)
strRange = "A" & y & ":L" & y
Me.Range(strRange).Select ' Just select for now
Exit Sub ' At most 1 row will be selected so I can exit now
End If
Next
End Sub

I'm sure I have nothing right since it does not work! Could someone guide
me in the right direction?

Thanks,
Andrew

--

Dave Peterson