Deleting content from a range of cells

Hi guys
I need your support.

i have this ceiling code, but if a select a range of cells to delete the content, the macro pop up an error.
how to delete a range of a cells without having error from macro?

Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
Dim cell As Range
Dim r As Range
'Stop
' cater for clearing entire column(s)
If target.Rows.Count = Me.Rows.Count Then Exit Sub
For Each r In target.Cells
If Not (Application.Intersect(target, Range("C:D")) _
Is Nothing) Then
    For Each cell In target
        With cell
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    Next 'cell
End If
   If Not (Application.Intersect(r, Range("E5:E10005")) Is Nothing) Then
            With r
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = "0:" & Application.Ceiling(Round(.Value * 60, 0), 15)
                    Application.EnableEvents = True
                End If
            End With
        End If
    Next r


End Sub

Hi, you have ' near your first Next, disabling the "cell". For each cell - - Next cell
Could that be the problem?

cell is commented out

Thank you so much, it works perfectly.

Originally Posted by TMS

You'll get a Type MisMatch, error 13, if you put a non-numeric character on column E.

Maybe this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim r As Range

'Stop

' cater for clearing entire column(s)
If Target.Rows.Count = Me.Rows.Count Then Exit Sub

For Each r In Target.Cells
    If Not Intersect(Target, Range("C:D")) Is Nothing Then
        For Each cell In Intersect(Target, Range("C:D"))
            With cell
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If '.HasFormula
            End With 'cell
        Next 'cell
    End If
    If Not Intersect(r, Range("E5:E10005")) Is Nothing Then
        With r
            If Not .HasFormula Then
                Application.EnableEvents = False
                On Error Resume Next
                .Value = "0:" & Application.Ceiling(Round(.Value * 60, 0), 15)
                On Error GoTo 0
                Application.EnableEvents = True
            End If '.HasFormula
        End With 'r
    End If
Next 'r

End Sub

Proper indenting and some "white space" will make it easier to read and check that all the blocks are started and ended correctly.

As has been suggested, On Error Resume Next will cater for the non-numeric entry in column E.

Commenting out the "cell" on "Next cell" has no effect. It is my understanding that the Next does not require a parameter and it is, I believe, more efficient to leave the parameter/variable out. However, for clarity, it is good practice to leave it there and comment it out rather than simply remove it.

Regards, TMS