VBA Excel for each loop issue

Question

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?

EDIT: Picture added for clarity

Instead of using Selection.FormatConditions, use ttlBr.FormatConditions if that is the number you want to format. Otherwise you are dependent on the location of the current cell selected. — Darrell H
– Darrell H, Commented Jul 13, 2017 at 12:28
What value do you have in columns T and H? Provide a sample please. TimeValue will set time, but date will be January 1 of the year 1. — Egan Wolf
– Egan Wolf, Commented Jul 13, 2017 at 12:29

A.S.H · Accepted Answer · 2017-07-13 13:00:05Z

3

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

edited Jul 13, 2017 at 13:00

answered Jul 13, 2017 at 12:39

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sbagnato Over a year ago

You are right, I want to compare the values in the same row. I think I understand what you are saying. The goal is so that if the person in the first row works between 8.5 and 9 hours but goes over break, I highlight the cell. If they work 9 or more hours, but go over break (a different break value since they worked a "full" day), then that gets highlighted too. How do I switch this around to compare correctly?

A.S.H Over a year ago

@sbagnato use the FormatConditions as indicated, and simply adjust that formula to what you need by applying it to cells H2 and T2. The Format conditions will auto-fill accordingly to the other rows since you're not using absolut ($) references. You can also consideradding the CF and its formula by Excel'GUI instead of VBA.

Harassed Dad · Accepted Answer · 2017-07-13 12:37:36Z

0

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

answered Jul 13, 2017 at 12:37

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

Collectives™ on Stack Overflow

VBA Excel for each loop issue

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related