Microsoft Office Forums - View Single Post

trevorc · 08-26-2018, 06:31 PM

So Here's my finished code to highlight a row in Yellow, then return any formatting that row when selecting another row. If anybody has any ideas on how to improve this let me know

Code:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Exit Sub
On Error Resume Next
Application.EnableEvents = False
current_cell = ActiveCell.Address
previous_row = Range("AC3").Value
C_Row = Selection.Row
If Selection.Row = Range("AB1").Value Then
   Application.EnableEvents = True
   Exit Sub
Else
'restore previous row formmatting
   Range("A78", "AL78").Select
   Selection.Copy
   Range("A" & Range("AC3").Value, "AL" & Range("AC3").Value).Select
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
   Range("A" & C_Row, "AL" & C_Row).Select
' save current row formatting
   C_Row = Selection.Row
   Range("A" & C_Row, "AL" & C_Row).Select
   Selection.Copy
   Range("A78").Select
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
'highlight current row
   Range("A" & C_Row, "AL" & C_Row).Interior.ColorIndex = 6
   Range("AJ1") = 6 'Selection.Interior.ColorIndex
   Range("A" & C_Row, "AL" & C_Row).Select
Range(current_cell).Select
Application.EnableEvents = True
Range("AB1").Value = Selection.Row
   Application.CutCopyMode = False
   Range("AC3").Value = Selection.Row
End If
End Sub