I wish to change the background colour of any selected cell on entry and
revert to original colour (none) on exit.

Thanks for the help so far Greg.

In both options I get a runtime error "Unable to set the ColorIndex property
of the interior class"

"Greg Wilson" wrote:

This version appears to adequately compensate for the problem described if
you need to only copy and paste values. You will need to set a reference to
the Microsoft Forms 2.0 Object Library for it to work. Paste all of the below
to the worksheet's code module. Minimal testing and never used personally:-

Dim copyval As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
If Len(copyval) 0 Then CopyToClip (copyval)
With Target
If .Count 1 Then Exit Sub
copyval = .Value
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 19
End With
End Sub

Private Sub CopyToClip(txt)
Dim DataObj As DataObject
Set DataObj = New DataObject
DataObj.SetText txt
DataObj.PutInClipboard
Set DataObj = Nothing
End Sub

Regards,
Greg

The worksheet is presumably protected. The following code unprotects, does
the job, then reprotects. If you are not using a password then you can delete
the password argument that follows the unprotect and protect statements. If
you are using a password then you need to substitute it for "mypassword":

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Unprotect "mypassword"
Cells.FormatConditions.Delete
If Len(copyval) 0 Then CopyToClip (copyval)
With Target
If .Count 1 Then Exit Sub
copyval = .Value
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
..FormatConditions(1).Interior.ColorIndex = 19
End With
Me.Protect "mypassword"
End Sub

Alternatively, if you are using xl2003 or later, there is probably
protection option that allows conditional formating changes thus letting you
avoid the above changes. I don't have access to xl2003 at this time to check
this out.

Regards,
Greg

"Redleg" wrote:

Thanks for the help so far Greg.

In both options I get a runtime error "Unable to set the ColorIndex property
of the interior class"

"Greg Wilson" wrote:

This version appears to adequately compensate for the problem described if
you need to only copy and paste values. You will need to set a reference to
the Microsoft Forms 2.0 Object Library for it to work. Paste all of the below
to the worksheet's code module. Minimal testing and never used personally:-

Dim copyval As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
If Len(copyval) 0 Then CopyToClip (copyval)
With Target
If .Count 1 Then Exit Sub
copyval = .Value
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 19
End With
End Sub

Private Sub CopyToClip(txt)
Dim DataObj As DataObject
Set DataObj = New DataObject
DataObj.SetText txt
DataObj.PutInClipboard
Set DataObj = Nothing
End Sub

Regards,
Greg

I suggest you substitute the following. The problem with the previous version
is that it will protect the wks even if it wasn't protected to begin with.
This one will only protect it if it was already protected. Otherwise it could
be a nuisance depending on what you are doing. The same comments hold
regarding the password.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim protected As Boolean
protected = Me.ProtectContents
If protected Then Me.Unprotect ' "mypassword"
Cells.FormatConditions.Delete
If Len(copyval) 0 Then CopyToClip (copyval)
With Target
If .Count 1 Then Exit Sub
copyval = .Value
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
..FormatConditions(1).Interior.ColorIndex = 19
End With
If protected Then Me.Protect ' "mypassword"
End Sub

Greg

"Redleg" wrote:

Thanks for the help so far Greg.

In both options I get a runtime error "Unable to set the ColorIndex property
of the interior class"

"Greg Wilson" wrote:

This version appears to adequately compensate for the problem described if
you need to only copy and paste values. You will need to set a reference to
the Microsoft Forms 2.0 Object Library for it to work. Paste all of the below
to the worksheet's code module. Minimal testing and never used personally:-

Dim copyval As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
If Len(copyval) 0 Then CopyToClip (copyval)
With Target
If .Count 1 Then Exit Sub
copyval = .Value
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 19
End With
End Sub

Private Sub CopyToClip(txt)
Dim DataObj As DataObject
Set DataObj = New DataObject
DataObj.SetText txt
DataObj.PutInClipboard
Set DataObj = Nothing
End Sub

Regards,
Greg

Great stuff Greg. Far more elegant than my attempts.
Protected was not reset if .Count 1.
Fixed that but removed the test in the end because I have some merged cells.

Thanks for your solution,

Redleg

"Greg Wilson" wrote:

I suggest you substitute the following. The problem with the previous version
is that it will protect the wks even if it wasn't protected to begin with.
This one will only protect it if it was already protected. Otherwise it could
be a nuisance depending on what you are doing. The same comments hold
regarding the password.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim protected As Boolean
protected = Me.ProtectContents
If protected Then Me.Unprotect ' "mypassword"
Cells.FormatConditions.Delete
If Len(copyval) 0 Then CopyToClip (copyval)
With Target
If .Count 1 Then Exit Sub
copyval = .Value
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 19
End With
If protected Then Me.Protect ' "mypassword"
End Sub

Greg

"Redleg" wrote:

Thanks for the help so far Greg.

In both options I get a runtime error "Unable to set the ColorIndex property
of the interior class"

"Greg Wilson" wrote:

This version appears to adequately compensate for the problem described if
you need to only copy and paste values. You will need to set a reference to
the Microsoft Forms 2.0 Object Library for it to work. Paste all of the below
to the worksheet's code module. Minimal testing and never used personally:-

Dim copyval As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
If Len(copyval) 0 Then CopyToClip (copyval)
With Target
If .Count 1 Then Exit Sub
copyval = .Value
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 19
End With
End Sub

Private Sub CopyToClip(txt)
Dim DataObj As DataObject
Set DataObj = New DataObject
DataObj.SetText txt
DataObj.PutInClipboard
Set DataObj = Nothing
End Sub

Regards,
Greg