detect ctrl-c was pressed (in copy mode) from vba?

[Mad Scientist Jr]

I am using color banding to highlight active cells as explained here:

Use color banding to locate the active cell
http://office.microsoft.com/en-us/assistance/HA011366231033.aspx

which I altered to highlight only horizontally, the current row from
cols A to Z

However, this has a side effect where if you press ctrl-c to copy a
cell or range of cells, and then click on a destination cell, ctrl-v no
longer pastes because the color banding code deselects the cells.

Is there a way to detect that ctrl-c has been pressed and excel is in
"copy mode" to disable the color banding, and then re-enable it after
the cells are pasted?

 

[Sandy]

Try playing around with this:

In your Worksheet_SelectionChange code place this at the beginning:

If Application.CutCopyMode = 1 Or _
Application.CutCopyMode = 2 Then
Call DisableBanding
End If

Then insert a new module and paste this to it:

Sub DisableBanding()
While Application.CutCopyMode = 1 Or _
Application.CutCopyMode = 2
Application.EnableEvents = False
DoEvents
Wend
Application.EnableEvents = True
End Sub


Sandy

 

[Jim Thomlinson]

Right from the article you posted...

There are two drawbacks to this method. First, as mentioned above, it is not
appropriate if you already have conditional formats. Second, the code tends
to clear the clipboard, so it becomes virtually impossible to copy and paste
while this code is running.

You can get the row liner addin from Chip if that helps...

http://www.cpearson.com/excel/RowLiner.htm

 

[gimme_this_gimme_that]

Thought I'd tag this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer

'// Note: Don't use if you have conditional
'// formatting 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 in case 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