Can anyone stop this running all the time?

[DUI]

Hi Guys,

The following code seems to run fairly well... but is there anyway to make it run only when the relevant cells are used. (you can see the curser think every time you move the active cell)


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False


'Expand Trade Columns
If Target.Column = 24 Or Target.Column = 25 Or Target.Column = 26 Then
Columns("Y:Z").EntireColumn.Hidden = False
Else: Columns("Y:Z").EntireColumn.Hidden = True
End If


Application.ScreenUpdating = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


If Target.Address = "$H$13" Or Target.Address = "$K$13" Then
Range("A13:A400").AutoFilter Field:=1, Criteria1:="1"
End If


Application.ScreenUpdating = True

End Sub

 

[xld]

It is already testing the cells to be specific.

 

[RoryA]

This might be a bit better:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'Expand Trade Columns
   Select Case Target.Column
      Case 24 To 26
         If Columns("Y:Z").EntireColumn.Hidden = True Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = False
            Application.ScreenUpdating = True
         End If
      Case Else
         If Columns("Y:Z").EntireColumn.Hidden = False Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = True
            Application.ScreenUpdating = True
         End If
   End Select
End Sub

 

[DUI]

This might be a bit better:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'Expand Trade Columns
   Select Case Target.Column
      Case 24 To 26
         If Columns("Y:Z").EntireColumn.Hidden = True Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = False
            Application.ScreenUpdating = True
         End If
      Case Else
         If Columns("Y:Z").EntireColumn.Hidden = False Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = True
            Application.ScreenUpdating = True
         End If
   End Select
End Sub

Your a GENIUS!.. I don't know why it works, but it does... It now doesn't think everytime I make a move. Thank You.

 

[RoryA]

It works by not trying to rehide the columns if they're already hidden and not turning screenupdating on and off unnecessarily. That helps to avoid the flicker.