VBA Time change

[BOGIA]

Hi everyone,

I'm trying to write a short statement to change the time if it is greater than 6:00:00 but it doesn't work. The data I have always 6:00:10 or more.

I have tried this but no good:

If range("a1") > 6:00:00 then
range("a1") = 6:00:00
End if

Could anyone please help! Thanks

TD

 

[Fat Cat]

you could try something like;

Sub ChangeTime()

Dim myTime As Date

myTime = Format(Cells(1, 1), "hh:mm:ss")
If myTime > "6:00:00" Then
    Cells(1, 1) = Format("6:00:00", "hh:mm:ss")
End If

End Sub

 

[BOGIA]

VBA time change

Thanks very much Fat Cat. I know I'm pushing my luck here but could you please do me one more favour!

Instead of one cell, I need to change more cells.
eg: a1, a2:a4, a6, a7:a12

Thanks again Fat Cat

 

[Fat Cat]

Try;

Private Sub CommandButton1_Click()
Dim myTime As Date
Dim LastRow As Long
Dim FirstRow As Long
Dim whichCol As String

FirstRow = 1        'set to the row you want to start looking from
LastRow = 13        'set to the row you want to stop looking at
whichCol = "A"        'set to the Column you want to look down

For i = FirstRow To LastRow
    Select Case i
    Case 5, 13      'put in here the rows not to look at
    Case Else       'it will look thru all the other rows
    
        myTime = Range(whichCol & i).Value
        If myTime > "06:00:00" Then
            Range(whichCol & i).Value = Format("6:00:00", "hh:mm:ss")
        End If
    End Select
Next i
End Sub

 

[BOGIA]

Thanks alots Fat Cat. It works. You are the champ...
Thanks again