VBA problems
 

Hello,

I just started messing around with VBA and I got something approaching
what I want (what I want is to highlight a row based on a value in the
1st column). This is highlighting the wrong row though! It looks like all
the numbers are correct... for instance, when I step through it, I'll get
the correct row selected but a different row highlighted! Please shed
some light on this! It's quite frustrating.

' first column has dates from 12/2003 to 12/2006
Private Sub Worksheet_Activate()
Const Yellow = 6
Dim row As String
Range("A1").Select
Do While ActiveCell.Value < ""
Selection.EntireRow.Interior.ColorIndex = xlColorIndexNone
If Month(ActiveCell.Value) = Month(Now) And _
Year(ActiveCell.Value) = Year(Now) Then
row = "A" & CStr(ActiveCell.row) & _
":L" & CStr(ActiveCell.row)
'MsgBox (row)
Selection.Range(row).Interior.ColorIndex = Yellow
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub



Thanks,
Andrew

Andrew,

Try this

Private Sub Worksheet_Activate()
Const Yellow = 6
Dim row As String
Range("A1").Select
Do While ActiveCell.Value < ""
Selection.EntireRow.Interior.ColorIndex = xlColorIndexNone
If Month(ActiveCell.Value) = Month(Now) And _
Year(ActiveCell.Value) = Year(Now) Then
row = "A" & CStr(ActiveCell.row) & _
":L" & CStr(ActiveCell.row)
'MsgBox (row)
Range(row).Interior.ColorIndex = Yellow
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew Clark" wrote in message
news:1111006416.e1b3dd5c4b4eb81284d055bf621b7496@t eranews...
Hello,

I just started messing around with VBA and I got something approaching
what I want (what I want is to highlight a row based on a value in the
1st column). This is highlighting the wrong row though! It looks like all
the numbers are correct... for instance, when I step through it, I'll get
the correct row selected but a different row highlighted! Please shed
some light on this! It's quite frustrating.

' first column has dates from 12/2003 to 12/2006
Private Sub Worksheet_Activate()
Const Yellow = 6
Dim row As String
Range("A1").Select
Do While ActiveCell.Value < ""
Selection.EntireRow.Interior.ColorIndex = xlColorIndexNone
If Month(ActiveCell.Value) = Month(Now) And _
Year(ActiveCell.Value) = Year(Now) Then
row = "A" & CStr(ActiveCell.row) & _
":L" & CStr(ActiveCell.row)
'MsgBox (row)
Selection.Range(row).Interior.ColorIndex = Yellow
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub



Thanks,
Andrew

Andrew

try it this way:

Const Yellow = 6
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
Range("A" & i).EntireRow.Interior.ColorIndex = xlColorIndexNone
If Month(Range("A" & i).Value) = Month(Now) And _
Year(Range("A" & i).Value) = Year(Now) Then
'MsgBox i
Range(Range("A" & i), Range("L" & i)).Interior.ColorIndex = Yellow
Exit Sub
End If
Next 'i

The "Selection.Range(row)...." is actually offsetting from the selected row.
The above method doesn't actually select the cells so it should be quicker
too.

Regards

Trevor


"Andrew Clark" wrote in message
news:1111006416.e1b3dd5c4b4eb81284d055bf621b7496@t eranews...
Hello,

I just started messing around with VBA and I got something approaching
what I want (what I want is to highlight a row based on a value in the
1st column). This is highlighting the wrong row though! It looks like all
the numbers are correct... for instance, when I step through it, I'll get
the correct row selected but a different row highlighted! Please shed
some light on this! It's quite frustrating.

' first column has dates from 12/2003 to 12/2006
Private Sub Worksheet_Activate()
Const Yellow = 6
Dim row As String
Range("A1").Select
Do While ActiveCell.Value < ""
Selection.EntireRow.Interior.ColorIndex = xlColorIndexNone
If Month(ActiveCell.Value) = Month(Now) And _
Year(ActiveCell.Value) = Year(Now) Then
row = "A" & CStr(ActiveCell.row) & _
":L" & CStr(ActiveCell.row)
'MsgBox (row)
Selection.Range(row).Interior.ColorIndex = Yellow
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub



Thanks,
Andrew

The portion of your code "Selection.Range(row)" is referring to a range
that is offset from the row of the selection. I.e., if the dates are in
A1:A4, and the first one with the current month and year is A3, then
Selection refers to A3, row is equal to 3, and Selection.Range(row) is
equivalent to Selection.Range("A3:L3"), which is A5:L5, i.e., A3:L3 of
the range with A3 as the upper left corner.

One way to fix it, which is not my preference but involves the least
change of your code, is to change the line beginning
"Selection.Range(row)" to Selection.Range(Cells(1), Cells(12))

which works so long as the relevant sheet is the active sheet.

This also makes superfluous all the lines including the variable "row"

Alan Beban

Andrew Clark wrote:
Hello,

I just started messing around with VBA and I got something approaching
what I want (what I want is to highlight a row based on a value in the
1st column). This is highlighting the wrong row though! It looks like all
the numbers are correct... for instance, when I step through it, I'll get
the correct row selected but a different row highlighted! Please shed
some light on this! It's quite frustrating.

' first column has dates from 12/2003 to 12/2006
Private Sub Worksheet_Activate()
Const Yellow = 6
Dim row As String
Range("A1").Select
Do While ActiveCell.Value < ""
Selection.EntireRow.Interior.ColorIndex = xlColorIndexNone
If Month(ActiveCell.Value) = Month(Now) And _
Year(ActiveCell.Value) = Year(Now) Then
row = "A" & CStr(ActiveCell.row) & _
":L" & CStr(ActiveCell.row)
'MsgBox (row)
Selection.Range(row).Interior.ColorIndex = Yellow
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub



Thanks,
Andrew