I have a macro that screens cells in a range and when the cell or it's adjacent cell is red or green, it assigns a value to another cell and it's adjacent cell in another worksheet. I have come this far that the first part works, however the second "looping" I can't figure it out myself. In other words, in the code below I want Range ("C1") and Range ("D1") to update to Range ("C2") and Range ("D2") and so on.
Sub AutoTrack()
Dim rng As Range
Dim cell As Range
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") =
WorksheetFunction.MRound(Worksheets("Track").Range("J2").Value + 0.125,
0.125)
Worksheets("Result").Range("C1") =
WorksheetFunction.MRound(Worksheets("Result").Range("D1") - 0.75, 0.125)
ElseIf
Worksheets("Track").Range("J2").DisplayFormat.Interior.Color = RGB(255, 0, 0)
Or Worksheets("Track").Range("I2").DisplayFormat.Interior.Color = RGB(255, 0,
0) Then
Worksheets("Result").Range("C1") = WorksheetFunction.MRound(Worksheets("Track").Range("I2") - 0.125, 0.125)
Worksheets("Result").Range("D1") =
WorksheetFunction.MRound(Worksheets("Result").Range("C1") + 0.75, 0.125)
End If
Next cell
End Sub
Aucun commentaire:
Enregistrer un commentaire