VBA Code on change of cell

cterjesen

Board Regular
Joined
Jun 23, 2003
Messages
119
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,206
See if this gets you what you want, instead of what you are using:

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

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,410
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top