Problems with Worksheet_Change

[Duncs]

I've got the following code in a sheet in my workbook:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngRowNum As Long

If Target.Column = 6 Then
If Target.Value = "Closed" Then
lngRowNum = Target.Row
Target.Range("B" & lngRowNum & ":I" &
lngRowNum).Interior.Color = vbYellow
End If
End If

End Sub

What it's supposed to do is, if you change the value held in column
6--'F'--on any row to 'Closed', it should colour the row from column
'B' to column 'I' in yellow.

So, for example, if I change the value held in F8 to 'Closed', it
should colour the background of cells B8 -> I8 in yellow. What it
does is, colours the background of cells G15 -> N15 in yellow!

Have I missed something, or do I misunderstand something?

Duncs

 

[Dave Peterson]

When you use syntax like this:

somecell.range("B1:I1")

It's actually using somecell as the starting point. So B1 is one cell to the
right of somecell.

So you could use:

me.Range("B" & lngRowNum & ":I" & lngRowNum).Interior.Color = vbYellow

Me is the worksheet that owns the code.

 

[Duncs]

This is your problem line.  When applying the Range property to a
Range object, the result is relative to that range object.

You probably want the "expression" to be the ActiveWorksheet.  So try:

Range("B" & lngRowNum & ":I" & lngRowNum).Interior.Color = vbYellow

(Also see Range.Range property in VBA Help)

Many thanks to you both. I've learned something new today!

Duncs