I need help figuring out how it's possible to highlight duplicates but not highlight or compare to values that has "IMD Exempel" in column A.

This is my code right now but it highlights every duplicate.

Dim Rng As Range
Dim cel As Range
Set Rng = Range(Range("C8"), Range("C" & Rows.Count).End(xlUp))
For Each cel In Rng
    If WorksheetFunction.CountIf(Rng, cel.Value) > 1 Then
        cel.Interior.Color = RGB(255, 255, 153)
    Else
        cel.Interior.ColorIndex = xlNone
    End If
Next cel

In this example screenshot I would like none to be highlighted.

Thanks in advance!

CodePudding user response：

Try using COUNTIFS():

Dim Rng As Range, Rng_A As Range
Dim cel As Range
Set Rng = Range(Range("C8"), Range("C" & Rows.Count).End(xlUp))
Set Rng_A = Range(Range("A8"), Range("A" & Rows.Count).End(xlUp))
For Each cel In Rng
    If WorksheetFunction.CountIfs(Rng, cel.Value, Rng_A, "<>IMD Exempel") > 1 Then
        cel.Interior.Color = RGB(255, 255, 153)
    Else
        cel.Interior.ColorIndex = xlNone
    End If
Next cel

Updated Code:

Dim Rng As Range, Rng_A As Range
Dim cel As Range, LastRow As Long, i As Long

LastRow = Cells(Rows.Count, "C").End(xlUp).Row

Set Rng = Range(Range("C8"), Range("C" & Rows.Count).End(xlUp))
Set Rng_A = Range(Range("A8"), Range("A" & Rows.Count).End(xlUp))

For i = 8 To LastRow
        If (WorksheetFunction.CountIfs(Rng, Cells(i, 3).Value, Rng_A, "<>IMD Exempel") > 1) And (Cells(i, 1).Value <> "IMD Exempel") Then
            Cells(i, 3).Interior.Color = RGB(255, 255, 153)
        Else
            Cells(i, 3).Interior.ColorIndex = xlNone
        End If
Next i

1. Have added extra condition to not highlight if A's value is IMD Exempel
2. As we cannot filter that A's value, we are using Cells() over range and tweaking the FOR loop a bit
3. We are using Lastrow manually and feeding into FOR loop

In the above code, I have added another condition to check i.e., <> IMD Exempel