For Loop with data formatted as Time (hh:mm:ss)

[Alex89]

Hi everyone,

I'm trying to create a for loop that iterates through each row in Column M. The data in this column is formatted as a time (Hours:Minutes:Seconds), I would like to flag any data (by highlighting the cell in red) greater than or equal to 7 minutes (00:07:00).

The code I have now essentially turns all of the data in column M into a number format, something like 0.0001. I'm using this format as the criteria for the For Loop. In this instance, 7 minutes is equivalent to 0.0048611111. After all of the iterations have been made I want to switch the format back to (H:M:S), with the cells over 7 minutes highlighted in red.

Sub QA_TimeSpent()


Dim ctr As Long






Worksheets("Master Publisher Content").Range("M:M").Select
Selection.NumberFormat = "0.00"


For i = 3 To ctr


If Range(i, 13) > 0.0048611111 Then
        Range(i, 13).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
    
Next i


 


Worksheets("Master Publisher Content").Range("M:M").Select
Selection.NumberFormat = "h:mm:ss"


End Sub

 

[steve the fish]

Why not use conditional formatting?