Change cell if wrong time entered

Giganews Newsgroups
Subject: Change cell if wrong time entered
Posted by:  Living the Dream (noodnu…@gmail.com)
Date: Wed, 7 Feb 2018

Hi Team

Trust you're all well.

I have fat fingers which, when excited tend to enter the time incorrectly whilst using the NumPad ( And No! before anyone asks, I won't be using the qerty numbers as workday is not that long.. :) ).

It's only ever one time extension that makes life miserable, anything with a 00:45, for example, if I want 09:45, I tend to enter it as 09:54.

I tried using a loop but I think the syntax is wrong.

Sub FixTime()

    Dim myRange As Range, c As Range
    Dim WrongMin As Integer, RightMin As Integer

    Set myRange = Sheets("TMS DATA").Range("AB6:AE250")
    WrongMin = Minute("00:54:00")
    RightMin = Minute("00:45:00")

    For Each c In myRange
        If Not c = "" Then
            With c
                If c = WrongMin Then c = RightMin
            End With
        End If
    Next c

End Sub

I was kinda hopeful someone could do two things here:

1. get the right syntax
2. change it so that I can insert it behind the sheet and use the SheetChange(ByVal)

I also tried using a couple of examples of the SheetChange but I couldn't get that to work either.

As always

TIA
Cheers
Mark.

Replies