FormatConditions appears to kill copypaste

[ctclinesmith]

I copied the code below from another thread here to highlight the
current row and column. It works great but kills the ability to copy a
cell or range. The "marching ants" disappear as soon as I change
cells. Any ideas of how to overcome this?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
iColor = 36
Else
iColor = iColor + 1
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" &
Target.Offset(-1, 0).Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub

 

[George Nicholson]

Untested, but maybe something like right after "On Error Resume Next" :
If Application.CutCopyMode <> False Then Exit Sub

CutCopyMode is the current status of the "marching ants". If on (either
xlCut or xlCopy) then your code is skipped. That should allow you to Paste.

HTH,

 

[Tom Ogilvy]

Don't use this code. Most code like this will clear the clipboard when you
have copied a range.