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

 

[Mavlon]

Does this macro do what you want...

[TABLE="width: 500"]
<tbody>[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]
</tbody>[/TABLE]

Yes! This started to work but then I got an error.

Run-time error'5':
Invalid procedure call or argument

Debug:
If Left(CurCell, InStrRev(CurCell, "-") - 1) = Left(PrevCell, InStrRev(PrevCell, "-") - 1) Then


Excel got through 384 items correctly before it stopped.
I wanted to mention that there are some items that don't have a "-", "cor", "enc" or "ena" in the number.
example line 385 the item number was 0X968D

 

[Rick Rothstein]

Excel got through 384 items correctly before it stopped.
I wanted to mention that there are some items that don't have a "-", "cor", "enc" or "ena" in the number.
example line 385 the item number was 0X968D

Yes, that is an important piece of information. Given that, does this modified code work for you...

[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 & IIf(InStr(Cells(X - 1, "B").Value, "-"), "", "-")
    CurCell = Cells(X, "B").Value & IIf(InStr(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]

 

[Mavlon]

Yes, that is an important piece of information. Given that, does this modified code work for you...

[TABLE="width: 500"]
<tbody>[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 & IIf(InStr(Cells(X - 1, "B").Value, "-"), "", "-")
    CurCell = Cells(X, "B").Value & IIf(InStr(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]
</tbody>[/TABLE]

Rick you are the man!!! Thank you so much !!

Cheers!!
Mav