I have defined conditional formating for a range of cells in my
spreadsheet. What I need is a macro to ensure that if a user copies and
pastes into these cells the conditional formating is not lost. Any
ideas greatly appreciated?

My efforts so far are to display a warning message and undo the action
as below, although it does not work for multiple cells. If I have one
cell in my range I can check that formatConditions.count 1 i.e there
is still some formatting. However the results are unpredictable if I
use a range of multiple cells.


Private Sub Worksheet_Change(ByVal Target As Range)

'Does the range still have conditional formating?

If HasValidation(Worksheets("Sheet1").Range("casefill ")) Then
Exit Sub
Else

MsgBox "Data paste not allowed please use paste special. " & _
"See help for further details", vbCritical

'Application.Undo

End If
End Sub

Private Function HasValidation(r) As Boolean
' Return True if every cell in Range r uses Conditional Formating

Dim x As Integer

x = 0
x = r.FormatConditions.count
Debug.Print "count: " & x
If x 0 Then HasValidation = True Else HasValidation = False

End Function