Hello All-

I have a macro code that higlights the row where the active cell is on, but
the problem is that it won't all me to copy and paste unless i use the Office
Clip board, which i don't want to use. I would like to be able to right
click Copy-Paste. Below is the code that I am using. Can anyone help?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow

.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub

One of the bad things about lots of macros is that they destroy the clipboard
when they run.

Maybe you could turn off events, then do the copy|paste, then turn events back
on.

Option Explicit
Sub TurnOffEvents()
application.enableevents = false
End Sub
Sub TurnOnEvents()
application.enableevents = True
End Sub


David T wrote:

Hello All-

I have a macro code that higlights the row where the active cell is on, but
the problem is that it won't all me to copy and paste unless i use the Office
Clip board, which i don't want to use. I would like to be able to right
click Copy-Paste. Below is the code that I am using. Can anyone help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow

.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub

--

Dave Peterson

Hey Dave-

I started to use your suggestion on another spreadsheet and it worked great.
However, when i try to share the workbook, it gives me an error message. Is
there anyway i can Enable Events and share the workbook at the same time?
Thanks.

"Dave Peterson" wrote:

One of the bad things about lots of macros is that they destroy the clipboard
when they run.

Maybe you could turn off events, then do the copy|paste, then turn events back
on.

Option Explicit
Sub TurnOffEvents()
application.enableevents = false
End Sub
Sub TurnOnEvents()
application.enableevents = True
End Sub


David T wrote:

Hello All-

I have a macro code that higlights the row where the active cell is on, but
the problem is that it won't all me to copy and paste unless i use the Office
Clip board, which i don't want to use. I would like to be able to right
click Copy-Paste. Below is the code that I am using. Can anyone help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow

.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub

--

Dave Peterson

I don't think it was the .enableevents that caused the heartache.

You can't change conditional formatting in a shared workbook. You're going to
have to look for a different solution.





David T wrote:

Hey Dave-

I started to use your suggestion on another spreadsheet and it worked great.
However, when i try to share the workbook, it gives me an error message. Is
there anyway i can Enable Events and share the workbook at the same time?
Thanks.

"Dave Peterson" wrote:

One of the bad things about lots of macros is that they destroy the clipboard
when they run.

Maybe you could turn off events, then do the copy|paste, then turn events back
on.

Option Explicit
Sub TurnOffEvents()
application.enableevents = false
End Sub
Sub TurnOnEvents()
application.enableevents = True
End Sub


David T wrote:

Hello All-

I have a macro code that higlights the row where the active cell is on, but
the problem is that it won't all me to copy and paste unless i use the Office
Clip board, which i don't want to use. I would like to be able to right
click Copy-Paste. Below is the code that I am using. Can anyone help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow

.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub

--

Dave Peterson


--

Dave Peterson

Hey...thanks for your response. I wanted to keep it as simple as possible.
The users of this worksheet are very simple users who prefer not to have turn
on and off the Events. I was hoping that with some modification of the code
below or even with a new coce, I would be able to copy/paste, or even use the
undo feature.

"David T" wrote:

Hello All-

I have a macro code that higlights the row where the active cell is on, but
the problem is that it won't all me to copy and paste unless i use the Office
Clip board, which i don't want to use. I would like to be able to right
click Copy-Paste. Below is the code that I am using. Can anyone help?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow

.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub