Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set KeyCells = Range("C9")
    Set isect = Application.Intersect(KeyCells, Range(Target.Address))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        isect.Value = isect.Value - 40
        Application.EnableEvents = True
    End If
End Sub

what I want to do is make it universal, ie, work for all sheets not just one. How to do it?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Move the code to the ThisWorkbook page

vba

and change the event to the Workbook_SheetChange event.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set KeyCells = Range("C9")
    Set isect = Application.Intersect(KeyCells, Range(Target.Address))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        isect.Value = isect.Value - 40
        Application.EnableEvents = True
    End If
End Sub
share|improve this answer
    
great, thank you –  FernandoSBS May 3 '13 at 18:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.