WorkSheet Change Events

[skd25]

I am using the macro below to trigger the code when some values in cells on Sheet 1 changes. However, I would like to have another change of events in Sheet 2 that will drive the output (results) on Sheet 1. Would like results on Sheet 1 to reflect a change in cells on Sheet 2. The range I have specified only reflects Sheet 1. Is there anyway I can make this happen?

Code on Sheet 1:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range

    Set KeyCells = Range("A1:B10")
    
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    Range("B2:B10").FormulaR1C1 = "=VLOOKUP(RC[-1],array,2,FALSE)"
    Range("B3:B10").value = Range("B3:B10").value
    Application.EnableEvents = True
    
End If
    
End Sub

 

[Tinbendr]

Is there anyway I can make this happen?

You just have to qualify the object.

    Sheet2.Range("B2:B10").FormulaR1C1 = "=VLOOKUP(RC[-1],array,2,FALSE)"
'or
    Worksheets(2).Range("B3:B10").value = Range("B3:B10").value
'or
    Worksheets("MySheetName").Range("B3:B10").value = Range("B3:B10").value

 

[AlphaFrog]

Try this in the Sheet2 code module.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)

    [color=darkblue]If[/color] [color=darkblue]Not[/color] Application.Intersect(Range("array"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]

        [color=darkblue]With[/color] Sheets("Sheet1").Range("B2:B10")
            Application.EnableEvents = [color=darkblue]False[/color]
            .FormulaR1C1 = "=VLOOKUP(RC[-1],array,2,FALSE)"
            .Value = .Value
            Application.EnableEvents = [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]

    [color=darkblue]End[/color] [color=darkblue]If[/color]

End [color=darkblue]Sub[/color]

 

[skd25]

thank you. it works great