Change background color
 

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

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

Dave Peterson wrote in
:

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:


I would, but I want to highlight the entire row, not just that cell.

Ah, you can get the whole row (or parts of the row to highlight):

Select say A2:L99 (with A2 the active cell).

Then format|Conditional formatting.
Use a formula that refers to the cell in column A of that row:

=TEXT($A2,"yyyymm")=TEXT($A$1,"yyyymm")

This will use the date value in A1 and compare it to the date in A2:A99. If the
year and month match, then A:L (of that row) will be formatted.

(I was confused over what cell you were using to compare all dates to.)


Andrew Clark wrote:

Dave Peterson wrote in
:

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:


I would, but I want to highlight the entire row, not just that cell.

--

Dave Peterson