Worksheet_SelectionChange

NoSparks · #8 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Convert a worksheet_SelectionChange to WorkBook_SheetChange macro	sparkle	Excel Programming	2	08-03-2014 02:26 AM