Code in Change event is not run.

[x7788]

Hallo Excel fans,
I have a programming problem I don’t seem to be able to solve on my own…?

What I have:
A row of cells each containing Data Validation. The user selects an item from the Data validation list. The adjacent cell determine, though an IF statement and the selected value, if a row should or should not be hidden.
The sheets Change event-routine is triggered and runs the following code which looks at each of these cells and hide/unhide the associated row depending if the outcome of the IF statement was TRUE or FALSE. Here is my code:

If Not Intersect(Target(1), PositionInputRange) Is Nothing Then
Application.ScreenUpdating = False

For i = 48 To 168 Step 2
If Range("H" & i) = True Then
Rows(i & ":" & i + 1).EntireRow.Hidden = True
Else
Rows(i & ":" & i + 1).EntireRow.Hidden = False
End If
Next

Application.ScreenUpdating = True
End If

The problem:
While the Change event routine is triggered and the code is executed, the rows are not hidden/unhidden accordingly.
If the code is added to a buttons Click event it runs fine…

Excel ver: 2002
OS: windows vista

Plenty thankx for your time...

 

[VoG]

Can you post the entire code using code tags

[code]
your code
[/code]

 

[x7788]

This is the entire event code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim resp As Integer
Dim c As Range
Dim r As Variant
Dim PositionInputRange As Range

Set PositionInputRange = Range("N48:N108,N124:N166")

'position input range
If Not Intersect(Target(1), PositionInputRange) Is Nothing Then

If Target(1) = "" Then Exit Sub

'check if the selected position exists
If IsError(Target(1).Offset(0, 1)) Then
resp = MsgBox("Position """ & Target(1) & """ was not found. " & Chr(13) & "Would you like to add it to the database?", vbYesNo)

If resp = 7 Then
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
Exit Sub
End If

'add new position to the db
Application.EnableEvents = False
Target = UCase(Target)
Sheets("new position").Range("D8") = UCase(Target)
Application.EnableEvents = True
Sheets("new position").Activate
Sheets("new position").Range("D10").Select
Else
Target.Offset(2, 0).Select
End If
End If

If Not Intersect(Target(1), PositionInputRange) Is Nothing Then
Application.ScreenUpdating = False

For i = 48 To 168 Step 2
If Range("H" & i) = True Then
Rows(i & ":" & i + 1).EntireRow.Hidden = True
Else
Rows(i & ":" & i + 1).EntireRow.Hidden = False
End If
Next

Application.ScreenUpdating = True
End If

End Sub