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