Repost - more info

[K Dales]

Posted the below yesterday. In testing further I found something curious:
As long as I type in the data the code works fine, and if there are already
numbers in the data range and I paste over them the code also works fine.
But if I paste over blank cells, the code fails. So why would Excel care if
the cells are already filled or not when pasting?

Original post:
I am using the following Worksheet_Change procedure to validate and organize
a data table as it is being edited. Works fine except when values are pasted
into the table. The procedure fires but it ends prematurely - stepping
through the code the error trapping is activated on the line CheckRange.Value
= Val(CheckRange.Text) but if I remove the On Error statement it does not
throw an error message(???). The error when I debug is "Application-defined
or object-defined error." Also, in trying a few things, I find that if I set
CutCopyMode to False anywhere inside this sub it will immediately revert back
to xlCopy - but the copy mode "marquee" around the cells copied has been
switched off.

Any ideas why this is happening???

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DataRange As Range, CheckRange As Range
Dim NonNumeric As Boolean

On Error GoTo Err

Application.EnableEvents = False
Application.ScreenUpdating = False

With Worksheets("DATA SHEET")
.Unprotect
Set DataRange = .Range("B:B")
If NameToRange("SelectedChart") > 2 Then Set DataRange = .Range("B:C")
NonNumeric = False
For Each CheckRange In Intersect(Target.Cells, DataRange)
If (CheckRange.Row > 1) And (CheckRange.Value <> "") And
IsNumeric(CheckRange.Text) Then
CheckRange.Value = Val(CheckRange.Text)
If NameToRange("SelectedChart") > 2 Then
If Val(Range("C" & CheckRange.Row)) <> 0 Then _
.Range("D" & CheckRange.Row).Formula = "=B" & CheckRange.Row
& "/C" & CheckRange.Row _
Else .Range("D" & CheckRange.Row).ClearContents
Else
.Range("D" & CheckRange.Row).ClearContents
End If
Else
NonNumeric = (CheckRange <> "")
CheckRange.ClearContents
If NameToRange("SelectedChart") > 2 Then
If Val(Range("C" & CheckRange.Row)) <> 0 Then _
.Range("D" & CheckRange.Row).Formula = "=B" & CheckRange.Row
& "/C" & CheckRange.Row _
Else .Range("D" & CheckRange.Row).ClearContents
End If
End If
Next CheckRange
.Protect
End With

Application.ScreenUpdating = True
Application.EnableEvents = True

If NonNumeric Then MsgBox "Data range can only contain numeric data;" _
& vbCrLf & "invalid text was deleted", vbInformation, "ERROR:"

Err:
Application.ScreenUpdating = True
Application.EnableEvents = True
Debug.Print Err.Number, Err.Description
End Sub