Simple for.. next problem

I need to use the Worksheet selectionchange event to trigger and action as
follows.

If the value in cell A1 on worksheet 1 changes then this value must be
looked up in Range 1 on worksheet 2. having found the corresponding value I
want to have a coloured shading apllied to the row in Range 2 in which the
target cell lies.

can you help please?

Hi Alan,
Hope this will help:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Stop
Z = Range("A1").Value
Sheets("Sheet2").Select
Application.Goto Reference:="Range1"
Selection.Find(What:=(Z), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End Sub


"Alan M" wrote:

> I need to use the Worksheet selectionchange event to trigger and action as
> follows.
>
> If the value in cell A1 on worksheet 1 changes then this value must be
> looked up in Range 1 on worksheet 2. having found the corresponding value I
> want to have a coloured shading apllied to the row in Range 2 in which the
> target cell lies.
>
> can you help please?

Hi Alan,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long
Dim oRng As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
Set oRng = Worksheets("Sheet2").Range("Range1")
iRow = Application.Match(.Value, oRng, 0)
If iRow > 0 Then
oRng.Cells(iRow, 1).Interior.ColorIndex = 6
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

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


"Alan M" <[email protected]> wrote in message
news:[email protected]...
> I need to use the Worksheet selectionchange event to trigger and action as
> follows.
>
> If the value in cell A1 on worksheet 1 changes then this value must be
> looked up in Range 1 on worksheet 2. having found the corresponding value
I
> want to have a coloured shading apllied to the row in Range 2 in which the
> target cell lies.
>
> can you help please?