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/as...366231033.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?

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


Mad Scientist Jr wrote:
> 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/as...366231033.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?

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
--
HTH...

Jim Thomlinson


"Mad Scientist Jr" wrote:

> 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/as...366231033.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?
>
>

You can also download workbook navigation addins from Excel Add-ins page
of below site.

http://in.geocities.com/shahshaileshs/


You can clik the options "Start Highliting Row". To Copy range of cells
select it with mouse, Copy & select the destination of range of cells
also with mouse & paste. To copy single cell, You need to terminate the
macro by "Stop Higlighting Row". Undo\Redo also works with this feature
on.

Regards,
Shah Shailesh
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)

*** Sent via Developersdex http://www.developersdex.com ***

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