Conditional formatting times with VBA

[ben.sullins]

Hello,

I am having some trouble formatting a time value in excel. I use th
<value>/86400 to get the decimal value which translates into a time.

It goes something like this:

81.94 / 86400 = 0.000948483

Which translates to 1:22 when in the m:ss format.

My situation is that often these values will be less then one minut
and I need to get rid of the leading zero. I have tried to us
conditional formatting in VBA but was unable to test for the valu
correctly. I'm working on this all the time so any clues would help
lot

 

[ben.sullins]

Here's the VBA I was using

Range("C:C").Select
If (Selection.Value * 86400 > 60) Then
Selection.NumberFormat = "m:ss"
Else
Selection.NumberFormat = "\:ss"
End If

I'm getting a type mismatch error on the first line of my if statement.

 

[Juan Sanchez]

Ben

you cannot evaluate a range for a value... you have to do
it cell by cell, try this:

Dim MyRange As Range
Set MyRange = ActiveSheet.Range("C1:C100")
For Each MyRange In MyRange
If (MyRange.Value * 86400 > 60) Then
MyRange.NumberFormat = "m:ss"
Else
MyRange.NumberFormat = "\:ss"
End If
Next MyRange

Note that I changed your C:C range for a smaller one,
since this will evaluate the entire column cell by cell,
if you use C:C thats 65000+ times

I tested it and worked fine...

Cheers
Juan

 

[ben.sullins]

Works Perfect!

Next step is to get this into a toolbar button that I can run on
spreadsheet without storing the actual module inside the workbook.

I'll reply if I run into problems.

Thanks!

Ben Sullin