Have worksheet refresh only for changes in certain cells

[echane]

Hi, I need help with changing the below code so the macro doesn't activate every time ANY cell is changed in the worksheet. I only need it to refresh the worksheet if the value in C5 changes. Right now it's really annoying because every cell I change will make the macro run again and throws me back to the top of the worksheet which is really inconvenient. Thanks!


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("C5").Value = "No" Then
Rows("122:152").Select
Selection.EntireRow.Hidden = True
Range("C5").Select
ElseIf Range("C5").Value = "Yes" Then
Rows("122:152").Select
Selection.EntireRow.Hidden = False
Range("C5").Select
End If

[GTO]

Greetings echane,

Try:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Address = "$C$5" Then
If Range("C5").Value = "No" Then
Rows("122:152").EntireRow.Hidden = True
Range("C5").Select
ElseIf Range("C5").Value = "Yes" Then
Rows("122:152").EntireRow.Hidden = False
Range("C5").Select
End If
End If
End Sub


Hope that helps,

Mark

[Bob Phillips]

Simpler



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Address = "$C$5" Then
Me.Rows("122:152").Hidden = Target.Value = "No"
End If
End Sub

[echane]

Thank you! You are all amazing! I'm learning things kinda bit by bit and this forum helps me out so much with that. :)