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,496
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,081,458
Messages
5,358,808
Members
400,513
Latest member
sdrowsick

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top