Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .