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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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
 

Forum statistics

Threads
1,141,144
Messages
5,704,544
Members
421,354
Latest member
Canary99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top