On Change Event and Intersect
Your code should be in the sheet rather than in ThisWorkbook. Right click the
sheet tab and select view code. Paste the following...
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("C13:C22")) Is Nothing Then _
Target.Offset(0, 2).Value = Target.Value * Target.Offset(0, 1).Value
ErrorHandler:
Application.EnableEvents = True
End Sub
Note that there are a couple of changes. If more than a single cell was
changed then nothing happens. If the change occured in C13:C22 then it turns
off events and makes the change which keeps the code from making a recursive
call based in the change that the code makes.
--
HTH...
Jim Thomlinson
"headly" wrote:
Have code that reads (thanks to ozgrid)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("c13:c22")) Is Nothing Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Value * ActiveCell.Offset(0,
1).Value
End If
but that effects the range c13:c22 on all sheets; How do I keep it specific
to a particular sheet?
TIA
|