CODEWITHSUNDEEP
vbaexcel
sbagnato
sbagnato

Reputation: 496

VBA Excel for each loop issue

I have the below code that is supposed to loop through a column of break times and a column of staff times. If the staff time is between 8:28 and 8:58, then if the break time is over 61 minutes, the cell should switch color. Likewise, if the staff time is over 8:58, if the break time is over 91 minutes, the cell should switch color. Right now, neither is happening, as something is obviously missing in the code.

    Dim ttlBr As Range, stfTm As Range
    Dim StfTm900 As Double, StfTm830 As Double, ttlBrTm900 As Double, ttlBrTm830 As Double
    StfTm900 = TimeValue("08:58:00")
    StfTm830 = TimeValue("08:28:00")
    ttlBrTm900 = TimeValue("01:31:00")
    ttlBrTm830 = TimeValue("01:01:00")
    For Each ttlBr In Range("T2:T7")
        For Each stfTm In Range("H2:H7")
            If stfTm > StfTm830 And stfTm < StfTm900 Then
                If ttlBr > ttlBrTm830 Then
                    Selection.FormatConditions(1).Interior.Color = 5263615
                End If
            ElseIf stfTm > StfTm900 Then
                If ttlBr > ttlBrTm900 Then
                    Selection.FormatConditions(1).Interior.Color = 5263615
                End If
            End If
        Next stfTm
    Next ttlBr

What am I missing?

enter image description here

EDIT: Picture added for clarity

Upvotes: 1

Views: 64

Answers (2)

A.S.H
A.S.H

Reputation: 29332

I think that the most important mistake is that you are doing two nested loops, whilch mean you are checking all cells of T2:T7 versus all cells of H2:H7. What you actually need is to compare cells on the same row, right? Also you are setting the FormatConditions(1).Interior.Color after checking, which doesn't make sense. Either set some FormatConditions or use Range.Interior.Color, but don't mix them.

The "subscript out of range" error is most likely due to FormatConditions(1) which does not exist.

Try with format conditions something like this instead of the loops:

  With Sheet1.Range("T2:T7").FormatConditions
    .Delete
    .Add(xlExpression, , _
    "=AND(T2>" & ttlBrTm830 & ", H2 <" & StfTm900 & ",H2 >" & StfTm830 & ")").Interior.Color = 5263615
  End With

Upvotes: 3

Harassed Dad
Harassed Dad

Reputation: 4704

The line

ttlBr.FormatConditions(1).Interior.Color = 5263615

will only work if the cell already has a format condition, otherwise it throws the error described

Upvotes: 0

Related Questions