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:

Highlighting_rows.jpg


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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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)



 
Upvote 0
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
 
Upvote 0
All I changed was range to Range("B2:B25475")

and this was result:

SAP_Text3.jpg


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?
 
Upvote 0
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:

SAP_Text4.jpg
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top