Prevent paste from wiping out conditional format?

[rcmodelr]

Due to some problems copy/paste causes if copying data from one cell with no conditional format to a cell containing conditional format... simple paste wipes out the destination's conditional format settings...

I'm trying to build a VBA macro that if trying to use "Paste" to change cell contents, on change, the macro would check for existing conditional formatting in the destination cell, and if destination cell contains conditional formatting, the macro would prevent normal "Paste" from working and change action to destination cell from "paste" to "paste values" so the value from the copied range would go into the destination, but the action would retain the conditional formatting already in the destination range.

How can I check whether the destination cell contains conditional formatting within my VBA macro?

Would like a way that will work in ALL versions of Excel from Excel 2000 through the newest version.

 

[JoeMo]

Here's a simple UDF you can use from your VBA routine to see if a cell has conditional formatting.

Function HasCF(R As Range) As Boolean
If R.FormatConditions.Count > 0 Then HasCF = True
End Function

Example of use of this function:

Sub CFCells()
'Lists format conditions for cells on active sheet that are conditionally formatted
Dim FC As FormatCondition, Ct As Long, strCFCells As String, c As Range, cfCellsCt As Long
Ct = ActiveSheet.UsedRange.FormatConditions.Count
If Ct > 0 Then
    For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 1)
        If HasCF(c) Then
            cfCellsCt = cfCellsCt + 1
            strCFCells = strCFCells & vbNewLine & c.Address
            For Each FC In c.FormatConditions
                MsgBox FC.Formula1
            Next FC
        End If
    Next c
    MsgBox "There are " & cfCellsCt & " Cells w/CF " & strCFCells
End If
End Sub