Conditional formatting times with VBA

  • Thread starter Thread starter ben.sullins
  • Start date Start date
B

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
 
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.
 
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
 
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
 
Back
Top