Excel Date/Time problem
 

I have written a macro designed to change the color and
bold all values of 40 seconds and above in a column. The
column looks like this:

0:00:02
0:00:56
0:00:03
etc.

For a number of reasons, I cannot use conditional
formatting and am attempting to do this via code.

Here is the macro:

Sub Summary1()

Dim test_name As String
Dim mynumber As Date

test_name = ActiveWorkbook.Name
mynumber = "0:00:39"

ActiveWorkbook.SaveAs Filename:="Z:\Excel\" & test_name
& " - Sum 1 test.xls"

Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Columns("L:O").Select
Selection.Delete Shift:=xlToLeft

Range("K3").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value mynumber Then
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub

The macro works and changes all cells with 00:00:40 and
above. However, it also includes cells with a value of
00:00:39 for some reason. As you might guess, I tried
changing the value of the mynumber variable to 0:00:40.
When I did this, only values of 0:00:41 and higher were
changed and cells with the actual value of 0:00:40 were
ignored. I am mystefied.

Excel Date/Time problem
 

0:00:39 = .000451388888889
0:00:40 = .000462962962963

half the numbers between those two values will display as 0:00:39 and half
as 0:00:40 (and also between 38 and 39 seconds). Any test of equality will
fail if they are not exactly equal. Using could result in mixed results.

Perhaps use a test like

If cDate(format(ActiveCell.Value2,"hh:mm:ss")) mynumber Then


--
Regards,
Tom Ogilvy


"Demian Valle" wrote in message
...
I have written a macro designed to change the color and
bold all values of 40 seconds and above in a column. The
column looks like this:

0:00:02
0:00:56
0:00:03
etc.

For a number of reasons, I cannot use conditional
formatting and am attempting to do this via code.

Here is the macro:

Sub Summary1()

Dim test_name As String
Dim mynumber As Date

test_name = ActiveWorkbook.Name
mynumber = "0:00:39"

ActiveWorkbook.SaveAs Filename:="Z:\Excel\" & test_name
& " - Sum 1 test.xls"

Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Columns("L:O").Select
Selection.Delete Shift:=xlToLeft

Range("K3").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value mynumber Then
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub

The macro works and changes all cells with 00:00:40 and
above. However, it also includes cells with a value of
00:00:39 for some reason. As you might guess, I tried
changing the value of the mynumber variable to 0:00:40.
When I did this, only values of 0:00:41 and higher were
changed and cells with the actual value of 0:00:40 were
ignored. I am mystefied.