VBA messing with conditional formatting

43 views Asked by At

I wrote the code below to archive resolved feedback issues however, when it runs although it works fine it screws with my "Traffic Light" conditional formatting. Is there a way for the code to do it's job but not effect the conditional formatting or maybe by deleting the rows after it pastes them within the VBA code itself?

Any help and advise would be greatly appreciated.

Please see the code below -

Sub ArchiveResolvedRows()

Dim wsF As Worksheet
Dim wsA As Worksheet
Dim lastRowF As Long
Dim lastRowA As Long
Dim i As Long

Set wsF = Worksheets("Feedback Tracker")
Set wsA = Worksheets("Archive")

lastRowF = wsF.Cells(Rows.Count, "A").End(xlUp).Row

For i = lastRowF To 2 Step -1 ' Loops through all rows in reverse order starting from the last row

    If InStr(1, wsF.Range("E" & i).Value, "Resolved", vbTextCompare) > 0 Then ' Checks if the value in column E contains "Resolved"

        lastRowA = wsA.Cells(Rows.Count, "A").End(xlUp).Row ' Gets the last row of sheet "Archive"

        wsF.Rows(i).Cut ' Cuts the row in the "Feedback Tracker" sheet
        wsA.Rows(lastRowA + 1).Insert Shift:=xlDown ' Inserts the row in the "Archive" sheet at the last row

    End If

Next i

End Sub

0

There are 0 answers