FormatConditions appears to kill copypaste

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

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,
--
George Nicholson

Remove 'Junk' from return address.

<[email protected]> wrote in message
news:[email protected]...
>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
>

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

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> 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
>