The easiest way might be to use offset and a counter which goes up by 1 each iteration of your loop.
If you want the offset to increase whether or not either condition is met then increment i outside the If.
Sub AutoTrack()
Dim rng As Range
Dim cell As Range
Dim i As Long
Set rng = Workbooks("Test").Worksheets("Track").Range("I2:I10")
For Each cell In rng
If cell.DisplayFormat.Interior.Color = RGB(146, 208, 80) Or cell.Offset(0, 1).DisplayFormat.Interior.Color = RGB(146, 208, 80) Then
Worksheets("Result").Range("D1").Offset(i) = WorksheetFunction.MRound(cell.Offset(, 1).Value + 0.125, 0.125)
Worksheets("Result").Range("C1").Offset(i) = WorksheetFunction.MRound(Worksheets("Result").Range("D1").Offset(i) - 0.75, 0.125)
i = i + 1
ElseIf cell.Offset(, 1).DisplayFormat.Interior.Color = RGB(255, 0, 0) Or cell.DisplayFormat.Interior.Color = RGB(255, 0, 0) Then
Worksheets("Result").Range("C1").Offset(i) = WorksheetFunction.MRound(cell - 0.125, 0.125)
Worksheets("Result").Range("D1").Offset(i) = WorksheetFunction.MRound(Worksheets("Result").Range("C1").Offset(i) + 0.75, 0.125)
i = i + 1
End If
Next cell
End Sub
SJR, if
"J2"and"I2"in the first lines of theIFandELSEIFstatements are static then they are OK. But the ranges in each second line are not static and must increment for each loop,@GMalc - I've just amended my code to remove all static references, but it may not be correct as the question is not clear in this regard. Not entirely sure what you mean by "must increment for each loop"?
SJR, just pointing out that you missed the Offset in `WorksheetFunction.MRound(Worksheets("Result").Range("C1")'
OK, thanks, I think it's there now.
@ SJR - Thanks, worked when once I added Offset(i) too at the right handside of the equal sign.