run macro when drop down box equals

[jpzachar]

I have the following macro that I want to run when cell M9 equals either
"AR1" or "AR2". Is this possible? thanks!

Sub Macro7()
If Range("M9").Value = "AR1" Then
Rows("60:89").Select
Selection.EntireRow.Hidden = False
Rows("90:112").Select
Selection.EntireRow.Hidden = True
Else
Rows("90:112").Select
Selection.EntireRow.Hidden = False
Rows("60:89").Select
Selection.EntireRow.Hidden = True
End If
End Sub

 

[Barb Reinhardt]

You're close. Right click on the worksheet tab where the data is being
changed and select View Code. Paste this in

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range

If Target.Count > 1 Then Exit Sub

Set myRange = Me.Range("M9")

If Intersect(Target, myRange) Is Nothing Then Exit Sub

If myRange.Value = "AR1" Then
On Error Resume Next
Me.Rows("60:89").EntireRow.Hidden = False
Me.Rows("90:112").EntireRow.Hidden = True
On Error GoTo 0
Else
On Error Resume Next
Me.Rows("90:112").EntireRow.Hidden = False
Me.Rows("60:89").EntireRow.Hidden = True
On Error GoTo 0

End If

End Sub


I added the On error statements becuase IIRC, when the cells are already
hidden and you try to hide them, you get an error.

 

[jpzachar]

Thank you!!!! so much

You're close. Right click on the worksheet tab where the data is being
changed and select View Code. Paste this in

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range

If Target.Count > 1 Then Exit Sub

Set myRange = Me.Range("M9")

If Intersect(Target, myRange) Is Nothing Then Exit Sub

If myRange.Value = "AR1" Then
On Error Resume Next
Me.Rows("60:89").EntireRow.Hidden = False
Me.Rows("90:112").EntireRow.Hidden = True
On Error GoTo 0
Else
On Error Resume Next
Me.Rows("90:112").EntireRow.Hidden = False
Me.Rows("60:89").EntireRow.Hidden = True
On Error GoTo 0

End If

End Sub


I added the On error statements becuase IIRC, when the cells are already
hidden and you try to hide them, you get an error.

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.