All,
I need help with this code. My goal's for it to copy and paste red cells from sheet1 to sheet2 and green cells to sheet3. The current issues I'm having are when the data is cleared from sheets2/3 the new information isn't posted begging at cell A2. Also the data is posted in a reverse order. For instance if cells A2 and A4 should be posted on Sheet2, it'll post A4 before A2. The current code I have was pieced together from similar examples online, as you can probably tell. I appreciate any help!
I need help with this code. My goal's for it to copy and paste red cells from sheet1 to sheet2 and green cells to sheet3. The current issues I'm having are when the data is cleared from sheets2/3 the new information isn't posted begging at cell A2. Also the data is posted in a reverse order. For instance if cells A2 and A4 should be posted on Sheet2, it'll post A4 before A2. The current code I have was pieced together from similar examples online, as you can probably tell. I appreciate any help!
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
Sheet2.Range("A1").CurrentRegion.Offset(1).ClearContents
Sheet3.Range("A1").CurrentRegion.Offset(1).ClearContents
For r = lr To 2 Step -1
If Range("A" & r).Interior.Color = RGB(255, 0, 0) Then
Rows(r).Copy Destination:=Sheets("Sheet2").Range("A" & lr2 + 1)
lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("A" & r).Interior.Color = RGB(0, 176, 80) Then
Rows(r).Copy Destination:=Sheets("Sheet3").Range("A" & lr3 + 1)
lr3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
End If
Range("A1").Select
Next r
End Sub