mercredi 3 juillet 2019

How to Loop Through one Range in VBA Excel and Update Another

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