Microsoft Office Forums - View Single Post

NoSparks · 09-04-2018, 01:55 PM

selecting where to paste the formatting back in is causing the SelectionChange to fire and leaving the row selected
try this

Code:

Sub Macro9()
  curcel = ActiveCell.Address(0, 0)
    If Sheets("Automation Data").Range("D13") = 1 Then          'turn on highlighting
       ActiveSheet.Shapes("TB3").TextFrame.Characters.Text = "Turn OFF" & vbCrLf & "Highlighting"
       Sheets("Automation Data").Range("D13") = 2
    Else                                                        ' turn off highlighting
       ActiveSheet.Shapes("TB3").TextFrame.Characters.Text = "Turn ON" & vbCrLf & "Highlighting"
       Sheets("Automation Data").Range("D13") = 1
       'restore current row highlighting
       Sheets("Automation Data").Range("A55", "AL55").Copy
       Application.EnableEvents = False     'disable events
       Range("A" & Sheets("Automation Data").Range("D14").Value, "AL" & Sheets("Automation Data").Range("D14").Value).Select
       Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       Range(curcel).Select                 'select original cell
       Application.EnableEvents = True      'reenable events
       Application.CutCopyMode = False
    End If
 End Sub

You should also put

Code:

Application.ScreenUpdating = False

at the beginning of Worksheet_SelectionChange and

Code:

Application.ScreenUpdating = True

at the end to stop screen flicker.