Simple for.. next problem

[Guest]

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?

 

[Guest]

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

 

[Bob Phillips]

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)