Using Worksheet_Change Event

[txfink]

I think this is an easy question, but never tried it before and having problems getting it to work so figured I'd pose the question. Any help will be greatly appreciated.

What I'm trying to do is run some code (a select case statement) when a specific cell changes. The code (the select case) works as I set it up to trigger with a button click and it worked fine. I need the code to run anytime the sheet changes. The code is in the worksheet with the data. I'm sure I'm just missing something but not sure what?

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("TargetCell").Value
Case Is ....
...
...
End Select
End Sub

 

[combo]

The procedure Worksheet_Change(ByVal Target As Range) fires when the formula (i.e. cell's contents, not resulting recalculation) changes in a given worksheet. It gives you, as a 'Target' argument, changed range (as Range). The typical code:
- checks if the Target is the range you are interested in,
- can temporarily switch off raising events if the code has to change cells.
An example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:B10")) Is Nothing Then
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
End If
End Sub

combo