VBA Code on change of cell

[cterjesen]

I have a data validation list in cell B15. i want this piece of code to run when that cell is changed:

Private Sub Worksheet_change(ByVal Target As Range)

If Intersect(Target, Range("$B$15")) Is Nothing Then
Exit Sub
Else
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("E18:EE18")
r.EntireColumn.Hidden = (r.Value = "No")
Next
Application.ScreenUpdating = True
End If
End Sub

Basically, it should look to line 18 and hide columns where a "no" value is shown. It doesn't work because I have two "ranges" in this code. How do I get around that?

Thanks

 

[Tom Urtis]

See if this gets you what you want, instead of what you are using:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$B$15" Then Exit Sub

Application.ScreenUpdating = False

Range(Columns(5), Columns(135)).Hidden = False

Dim xColumn&
For xColumn = 5 To 135
If Cells(18, xColumn).Value = "No" Then Columns(xColumn).Hidden = True
Next xColumn

Application.ScreenUpdating = True

End Sub