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