VBA Time change

Status
Not open for further replies.

BOGIA

Active Member
Joined
May 23, 2005
Messages
266
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,118,070
Messages
5,570,018
Members
412,304
Latest member
citrus
Top