How to color every row with the same serial #?

Question

I have data as follows:
1 / 123456 / $100.00
2 / 123456 / $200.00
3 / 122222 / $200.00
4 / 123477 / $200.00
4 / 123477 / $200.00
4 / 123477 / $200.00
I would like to know if anyone knows a way I can automatically filter the repeated serial numbers, such as 123456 and 123477 without coloring or configuring the unrepeated ones. So I would have all 123456 serial number rows colored & all 123477 colored too but the 122222 would be untouched. It doesnt have to be colored, it can be customized to whichever way you think is appropiate. As long as I can separate or filter the numbers that are repeated form the ones that are not repeated. I have like 4500 lines & I need to go line by line figuring out which numbers I have to color or filter. So any help will be good. Thanks

garbo7441 · Accepted Answer

Edit:  If you just want to filter the values so that only values appearing once in the column are visible, you can do that with this 'toggle' macro.

It assumes your values are in column B, based on your question.  Copy the macro to the clipboard:

Sub Custom_Filter()
Dim i, LastRow
LastRow = Range("B" & Rows.Count).End(xlUp).Row
If ActiveSheet.Rows. SpecialCells(xlCellTypeVisible). Count >= 16777216 Then
For i = 1 To LastRow
If Application. CountIf(Range("B:B"), Cells(i, "B")) > 1 Then
Cells(i, "B").EntireRow.Hidden = True
End If
Next
Else
ActiveSheet. UsedRange.Rows.Hidden = False
End If
End Sub

Press ALT + F11

INSERT > MODULE

Paste the macro into the module editing area.

Close the VBE and return to Excel.

Press ALT + F8

When the Macros window opens, highlight the macro and click 'Options..'.

Enter a letter to be used as a keyboard shortcut and click 'OK'.

Close the Macros window.

Press CTRL + your shortcut letter to run the macro.  Each iteration of the macro will alternately show the complete list and the filtered list.

===================================
There are a couple of ways to approach this.  You could use Conditional Formatting, but that would seem a bit cumbersome for your situation.

I would approach it with a macro.  The following macro will allow you to proactively add an interior color to all cells with specified serial numbers, or to remove the interior color if you no longer wanted it.

The following assumes your serial numbers are in column B.  If not, modify the macro:
Change all "B" references to your column letter, i.e. "C", "L", etc.
Change the "B:B" reference to your column letter, i.e. "C:C", "L:L", etc.

Then copy the macro to the clipboard:

Sub Set_Interior_Color()
Dim i, LastRow
LastRow = Range("B" & Rows.Count).End(xlUp).Row
here:
selSerial = InputBox("Please enter the Serial Number to highlight.", _
"Select Serial Number")
If selSerial = "" Then
Exit Sub
End If
If Application.CountIf(Range("B:B"), selSerial) = 0 Then
MsgBox "Unable to locate Serial Number " & selSerial, vbOKOnly, "Not Found"
Exit Sub
End If
setColor = InputBox("Please enter the color index for " & selSerial & _
Chr(10) & Chr(10) & "15 = Lt Gray" & Chr(10) & "34 = Lt Turquoise" & _
Chr(10) & "35 = Lt Green" & Chr(10) & "36 = Lt Yellow" & Chr(10) & _
"37 = Lt Blue" & Chr(10) & "39 = Lavender" & Chr(10) & "40 = Tan" & _
Chr(10) & Chr(10) & "X = None (White)", "Select Color")
If setColor = "X" Then
setColor = xlNone
End If
For i = 1 To LastRow
If Cells(i, "B").Value = Val(selSerial) Then
Cells(i, "B").Interior.ColorIndex = Val(setColor)
End If
Next
oneMore = MsgBox("Do you wish to highlight another Serial Number?", _
vbYesNo, "One More?")
Select Case oneMore
Case Is = vbYes
GoTo here
Case Is = vbNo
End Select
End Sub

Press ALT + F11

In the menus at the top of the VBE, select INSERT > MODULE

Paste the macro into the module editing area to the right.

Close the VBE and return to Excel.

Press ALT  + F8

When the Macros window opens, highlight this macro and click 'Options..'

Enter a letter to be used as a keyboard shortcut letter and click 'OK'.

Close the Macros window.

Press CTRL + your shortcut letter to run the macro as needed.

Trending News

How to color every row with the same serial #?

1 Answer