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","a4:a6","a7","a10:a15") > 6:00:00 then
range("a1","a4:a6","a7","a10:a15") = 6:00:00
End if

Could anyone please help! Thanks

TD

 

[Derek]

Hi there

Try this in a standard module:

Sub ResetTime()
For Each cell In Range("a1, a4:a6, a7, a10:a15")
If cell.Value > TimeValue("6:00:00") Then cell.Value = TimeValue("6:00:00")
Next
End Sub


regards
Derek

PS Didn't realise you had 2 threads going with the same question. Best to stick to the original thread or it gets confusing for everyone.

 

[BOGIA]

Thanks Derek. You are a great helper. It works

 

[Von Pookie]

Duplicate.
http://www.mrexcel.com/board2/viewtopic.php?t=179574