Coloring Cells based on multiple criteria

Question

I have a spreadsheet that we are constantly adding data to. This data is imported from a report and added to the end of the spreadsheet. I have a macro already in place to remove duplicates. There is another macro that will highlight specific rows based on one of the cells contents, and then make a copy of the row and paste it into another sheet within the workbook. One of the columns requires a number as it's data. However, on occasion, this number is not available and we input "RCA Pending" into that cell.

What I need to do is have that cell highlighted in red. But, once the number is input into the cell, I need the cell color to change back to 'no fill', unless that row is highlighted from the previous macro that was run.

Expected result sample

I am not opposed to combining these macros if that is easier.

Here is the first macro listed above:

' This part highlights all rows that are Disputed  
' Keyboard Shortcut: CTRL+SHIFT+L

    Dim row As Range
    For Each row In ActiveSheet.UsedRange.Rows
        If row.Cells(1, "F").Value = "After Dispute For SBU" Then
            row.Interior.ColorIndex = 6
        Else
            row.Interior.ColorIndex = xlNone
        End If
    Next row

' This part clears the Disputed worksheet and copies all disputed rows to the sheet

With ThisWorkbook.Worksheets("Disputed")
 Range(.Range("A2"), .UsedRange.Offset(1, 0)).EntireRow.Delete
 End With

Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).row
lr2 = Sheets("Disputed").Cells(Rows.Count, "A").End(xlUp).row
For r = lr To 2 Step -1
    If Range("F" & r).Value = "After Dispute For SBU" Then
        Rows(r).Copy Destination:=Sheets("Disputed").Range("A" & lr2 + 1)
        lr2 = Sheets("Disputed").Cells(Rows.Count, "A").End(xlUp).row
    End If

    Range("A2").Select
Next r
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
End Sub

Answer 1

How about just using conditional formatting on the data. You would use a formula like

=$A2="RCA Pending"

which assumes that the data starts in A2 and the column in question is A. You would need to select all of the columns in all of the rows, starting at A2, and then apply the CF