Namastey and greetings experts!
I am writing a SelectionChange event macro to trigger it on each selection change. This has several changes in a range and mostly to hide lines and update formula in col D cells.
My learning point here is that this is basis the need I have, written it using online expert resources like these, and built it in bits and pieces. The code runs fine, but causes the screen update a lot and jump a lot.
When I tried changing this to Change from SelectionChange event, the functionality doesn't work.
So, requesting suggestions from you all experts to guide me to make it work better without making the screen jump and take time as it does on my code.
Here are the code lines:-
Note: this code is inside the Sheet, and not in a module, for it to trigger on each change.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''
'''''1. Macro 1
''''
''''If Not Intersect(Target, Range("$A1:AG100")) Is Nothing Then
''''
''''
'''' If Range("D7").Value = "Con1" Then
'''' Rows("29:29").EntireRow.Hidden = True
'''' Else
'''' Rows("29:29").EntireRow.Hidden = False
'''' End If
''''
''''End If
''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''
'''''2. Hidden
''''
''''If Not Intersect(Target, Range("$A1:AG100")) Is Nothing Then
''''
'''' If Range("D7").Value = "Con3" Then
'''' Rows("28:28").EntireRow.Hidden = True
'''' Else
'''' Rows("28:28").EntireRow.Hidden = False
'''' End If
''''
''''End If
''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'3. Tagging 2
If Not Intersect(Target, Range("$A1:AG100")) Is Nothing Then
If Range("D9").Value = "Con1" Then
Range("D10") = "INTL"
Rows("11:13").EntireRow.Hidden = False
Else
Rows("11:14").EntireRow.Hidden = True
If Range("D9").Value = "Con4" Then
Range("D10") = "INTL"
Rows("11:14").EntireRow.Hidden = False
Else
Rows("11:14").EntireRow.Hidden = True
End If
End If
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'4. Tagging 3
If Not Intersect(Target, Range("$A1:AG100")) Is Nothing Then
If Range("D21").Value = "" Or Range("D21").Value = "No" Then
Rows("22:24").EntireRow.Hidden = True
Columns("F:F").EntireColumn.Hidden = True
Else
Rows("22:24").EntireRow.Hidden = False
Columns("F:F").EntireColumn.Hidden = False
End If
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'5. Hiding Line
If Not Intersect(Target, Range("$A1:AG100")) Is Nothing Then
If Range("D9").Value = "abc" And Range("D10").Value = "123" Then
Rows("17:18").EntireRow.Hidden = True
Else
Rows("17:18").EntireRow.Hidden = False
End If
End If
*
*
*
*
'10. Hiding ComboBox2
If Not Intersect(Target, Range("$A1:AG100")) Is Nothing Then
' Application.ScreenUpdating = False
If Range("D10").Value = "INTL" Or Range("D10").Value = "Con4" Then
Me.ComboBox2.Visible = False
Else
Me.ComboBox2.Visible = True
End If
' Application.ScreenUpdating = True
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Display More
If there is any scope of performance improvement in this code, please suggest. Your guidance is highly appreciated.