Worksheet_SelectionChange

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

trevorc · #2 09-04-2018, 03:03 PM

Thanks for the prompt reply, I'll try out your suggestions later today, I'm swamped with work after taking 2 days off.

regards
Trevor

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