High light only "Expired" names that ARE NOT duplicates

question1.png

Hello everyone ! Im new to excel MACRO and working on a project.

I need to highlight "expired" names that are not duplicates. How can I tell my code to highlight non duplicate "expired" names?
In the example above, I DO NOT want Lauren highlighted because she is a duplicate.

Sub Test2()

Dim count, i As Long

count = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row
i = 2
Do While i <= count

    If Cells(i, 3).Value Like "Expired" Then
    Range(Cells(i, 1), Cells(i, 5)).Interior.Color = RGB(230, 98, 76)
    
    ElseIf Cells(i, 3).Value Like "New" Then
    Range(Cells(i, 1), Cells(i, 5)).Interior.Color = RGB(118, 234, 62)
    
    End If
    
    i = i + 1
    Loop

End Sub

Just in case, do you really need a macro for this? You can do this with formulas
Add an extra column with COUNTIF function, and calculated number of entries for each name
Then set conditional formatting for rows where COUNTIF = 1 and Status = Expired.


With a macro,you can do it with something this

Sub Test3()

Dim count, i As Long


Dim rng As Range
Set rng = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(UsedRange.Rows.count, 1))
rng.Select

count = UsedRange.Rows.count
i = 2
Do While i <= count
  
    If Cells(i, 3).Value Like "Expired" And Application.WorksheetFunction.CountIf(rng, Cells(i, 1)) = 1 Then
    Range(Cells(i, 1), Cells(i, 5)).Interior.Color = RGB(230, 98, 76)

    ElseIf Cells(i, 3).Value Like "Current" Then
    Range(Cells(i, 1), Cells(i, 5)).Interior.Color = RGB(118, 234, 62)

    End If

    i = i + 1
    Loop

End Sub

I think you could probably include the COUNTIF in the CF formula.

This formula would likely do it for conditional formatting:

Formula:

=AND(COUNTIF($A:$A,$A2)=1,$C2="Expired")

In the "applies to", either something like =$A$2:$C$1000 (or whatever row is low enough), or if the list will be really long and growing, you could use =$A:$C

Conditional Formatting, New Rule, Use formula to determine chich cells to format, then paste the formula, click format, choose the fill tab, and pick your color. You can then edit the rule (manage), and edit the "Applies to" field