Created
February 18, 2021 17:59
-
-
Save haider00727/21b0a25d6419d165005121b939baf2d1 to your computer and use it in GitHub Desktop.
Highlight Duplicate Values In A Column With Different Colors By Using
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. | |
2. Click Insert > Module, and paste the following code in the Module Window. | |
Sub ColorCompanyDuplicates() | |
'Updateby Extendoffice | |
Dim xRg As Range | |
Dim xTxt As String | |
Dim xCell As Range | |
Dim xChar As String | |
Dim xCellPre As Range | |
Dim xCIndex As Long | |
Dim xCol As Collection | |
Dim I As Long | |
On Error Resume Next | |
If ActiveWindow.RangeSelection.Count > 1 Then | |
xTxt = ActiveWindow.RangeSelection.AddressLocal | |
Else | |
xTxt = ActiveSheet.UsedRange.AddressLocal | |
End If | |
Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8) | |
If xRg Is Nothing Then Exit Sub | |
xCIndex = 2 | |
Set xCol = New Collection | |
For Each xCell In xRg | |
On Error Resume Next | |
xCol.Add xCell, xCell.Text | |
If Err.Number = 457 Then | |
xCIndex = xCIndex + 1 | |
Set xCellPre = xCol(xCell.Text) | |
If xCellPre.Interior.ColorIndex = xlNone Then xCellPre.Interior.ColorIndex = xCIndex | |
xCell.Interior.ColorIndex = xCellPre.Interior.ColorIndex | |
ElseIf Err.Number = 9 Then | |
MsgBox "Too many duplicate companies!", vbCritical, "Kutools for Excel" | |
Exit Sub | |
End If | |
On Error GoTo 0 | |
Next | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment