Highlight entire row if duplicates exist in the same column

Mavlon

New Member
Joined
Nov 2, 2015
Messages
16
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
 

Mavlon

New Member
Joined
Nov 2, 2015
Messages
16
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)



 

thes4s67

Board Regular
Joined
Aug 17, 2015
Messages
186
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.

Code:
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

New Member
Joined
Nov 2, 2015
Messages
16
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

New Member
Joined
Nov 2, 2015
Messages
16
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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
 
Last edited:

Mavlon

New Member
Joined
Nov 2, 2015
Messages
16
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,564
Office Version
2010
Platform
Windows
Does this macro do what you want...
Code:
[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]
 

Forum statistics

Threads
1,085,294
Messages
5,382,755
Members
401,804
Latest member
RB85

Some videos you may like

This Week's Hot Topics

Top