Highlight entire row if duplicates exist in the same column

[Mavlon]

I am trying to highlight an entire row if there are duplicates in Column B.
The duplicates can either have COR, ENC, or ENA at the end of the text.

Here is an example of how I need it to look:

The numbers are not sequential in Column B.
The next Item No could be SFC.....COR, Blue123-ENC, etc...

Does anyone know how I can do this??


Thanks,
Mav

 

[sheetspread]

Conditional format with a countif

 

[Mavlon]

I've tried different COUNTIF scenarios, but I can't get it right. I need it to assume COR, ENC, and ENA are the same or exclude them from the search.

I tried this but it doesn't work right.

=INDEX($B2:$B25475,ROW())<>INDEX($B2:$B25475,ROW()-1)

 

[Mavlon]

I can't figure it, someone please help.

 

[thes4s67]

I have asked a similar question like this on the forum.

Here is the code, it will require some tweaking and additional code to fit your requirements but the idea is the same.

Sub color_dups()

Dim d As Object, a, cell As Range, rng As Range
Set d = CreateObject("scripting.dictionary")
Set rng = Range("C2:C4878")


For Each cell In rng.Cells
    If Len(cell) > 0 Then d(cell.Value) = d(cell.Value) + 1
Next


For Each cell In rng.Cells
    If d(cell.Value) > 1 Then cell.Interior.ColorIndex = 37
Next


End Sub

 

[Mavlon]

All I changed was range to Range("B2:B25475")

and this was result:

I need it to be like original post. Maybe something different would work like this:

Every time a value changes in Column B, Highlight row to a different color (swapping between 2 colors) in my case the two colors used were Blue and white (see first post)

Does this make sense?

 

[Mavlon]

I tried this code and the results were close:

Sub Find_Duplicate_Entry()
Dim cel As Variant
Dim myrng As Range
Dim clr As Long
Set myrng = Range("B2:B" & Range("B25476").End(xlUp).Row)
myrng.Interior.ColorIndex = xlNone
clr = 3
For Each cel In myrng
If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
If WorksheetFunction.CountIf(Range("B2:B" & cel.Row), cel) = 1 Then
cel.Interior.ColorIndex = clr
clr = clr + 1
Else
cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
End If
End If
Next
End Sub


I received an error when running this. Run time error '9' Subscript out of range
cel.Interior.ColorIndex = clr

It started running and the result was close but needing changes:

Entire row needs to be highlighted
Only need 2 interchanging colors, not all colors
COR, ENC, and ENA need to somehow be excluded from the duplicate search (see first post)

Here is the result from this code until it errored out:

 

[sheetspread]

Highlight A5:E23, conditional formatting, new rule, use a formula to determine which cells to format, format values where this formula is true: =COUNTIF($B$5:$B$23,$B5)>1, format button, fill, choose blue or any color.


I tested this and it works, I can also post screenshots if it helps

 

[Mavlon]

I tried this, but it highlights all the same color. I need it to swap between two colors when it gets to a new part number that has duplicates.
The duplicates need to ignore part of the number at the end (COR,ENC or ENA). See my first post.

Lines 11-14 are same number but have ENC and COR at the end. They get highlighted together and then the next number would be a different color.

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub AlternateColoring()
  Dim X As Long, PrevCell As Variant, CurCell As Variant
  For X = 3 To Cells(Rows.Count, "B").End(xlUp).Row
    PrevCell = Cells(X - 1, "B").Value
    CurCell = Cells(X, "B").Value
    If Left(CurCell, InStrRev(CurCell, "-") - 1) = Left(PrevCell, InStrRev(PrevCell, "-") - 1) Then
      Cells(X, "B").EntireRow.Interior.ColorIndex = Cells(X - 1, "B").Interior.ColorIndex
    Else
      Cells(X, "B").EntireRow.Interior.ColorIndex = 6 + xlColorIndexNone - Cells(X - 1, "B").Interior.ColorIndex
    End If
  Next
End Sub[/td]
[/tr]
[/table]