Highlight the cell value/value

sasi

Board Regular
Joined
Jun 28, 2010
Messages
61
How can i highlight the cell value/cell when the value is repeated more than one time in column A when i run the macro
Column A
GRM155R71C104KA88D
USB3320C-EZK
TPS76318DBVT
MAX3051ESA+
ERJ-1TYF102U
RC0603FR-0749R9L
SN74AVC4T774RSVR
Column A
GRM155R71C104KA88D
USB3320C-EZK
TPS76318DBVT
GRM155R71C104KA88D
MAX3051ESA+
ERJ-1TYF102U
RC0603FR-0749R9L
SN74AVC4T774RSVR
GRM155R71C104KA88D
MAX3051ESA+
SN74AVC4T774RSVR
REpeated values may be more than one in column A,macro should take recognize all type of values numeric,alphanumeric,special characters like +_-<.>/?}]\*&^%$#@~,spaces....
Can any one provide the macro
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
What version of Excel are you using? The way to do it is by conditional formatting which is really easy with Excel 2007 onwards.
 

sasi

Board Regular
Joined
Jun 28, 2010
Messages
61
I am using both Excel-2003 and Excel-2007, vba is the ideal way for my solution
 

sasi

Board Regular
Joined
Jun 28, 2010
Messages
61
I am able to acheive with the following code,how to track the duplicates in columnA and show in message box.
Is it possible to recognize the Duplicate/repeated values with Cell Number, so that user can easily identify and remove the duplicates, some thing following way
The following PartNumbers are duplicated:ASD-123(Cell A5),DFG-123(Cell A45).....

Can any one help me on this.

Code:
Sub TestForDuplicates()
   Dim oCell As Range
   Dim oDuplicateCells As Range
   Dim sMsg As String
   On Error Resume Next
   For Each oCell In Intersect(Sheet2.UsedRange, Sheet2.Range("A:A")).Cells
       If Application.WorksheetFunction.CountIf(Intersect(Sheet2.UsedRange, Sheet2.Range("A:A")), oCell.Value) > 1 Then
           If oDuplicateCells Is Nothing Then
               Set oDuplicateCells = oCell
           Else
               Set oDuplicateCells = Union(oDuplicateCells, oCell)
           End If
           sMsg = sMsg & oCell.Value & ","
       End If
   Next
   If Len(sMsg) > 0 Then
       sMsg = Left(sMsg, Len(sMsg) - 1)
       oDuplicateCells.Select
       MsgBox "The following PartNumbers are duplicated: " & sMsg
   End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,112,995
Messages
5,543,183
Members
410,584
Latest member
Bluefox68
Top