Ceiling function and drag and drop

Private Sub Worksheet_Change(ByVal target As Range)

If Not Intersect(target, Union(Range("C:D"), Range("E5:E10005"))) Is Nothing Then
    Dim r As Range

    For Each r In target.Cells
        If Not Application.Intersect(target, Range("C:D")) Is Nothing Then
            With r
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If
            End With
        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 If

End Sub

Your code is designed to cater for a single cell (Target cell) change. If you drag a formula down a column (of interest) then you will be making changes to multiple cells ...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

'Stop

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

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(Target, Range("E5:E10005")) _
    Is Nothing) Then
    For Each cell In Target
        With cell
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = "0:" & Round(.Value * 60, 0)
                Application.EnableEvents = True
            End If
        End With
    Next 'cell
End If
End Sub

Regards, TMS

Originally Posted by TMS

Your code is designed to cater for a single cell (Target cell) change. If you drag a formula down a column (of interest) then you will be making changes to multiple cells ...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

'Stop

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

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(Target, Range("E5:E10005")) _
    Is Nothing) Then
    For Each cell In Target
        With cell
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = "0:" & Round(.Value * 60, 0)
                Application.EnableEvents = True
            End If
        End With
    Next 'cell
End If
End Sub

Regards, TMS

Thanks so much, its working perfectly the first problem if had.
Please, explain to me how to avoid this for next time I may create a macro.