Results 1 to 7 of 7

Thread: VBA Code To Highlight Duplicate Values In A Column

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    VBA Code To Highlight Duplicate Values In A Column

    Hello,

    My current vba code is finding the duplicate strings however only if they are in a sequence which is one after another. If it is out of sequence it does not work. So if the word Walter is repeated one after another it works. However if you put John in between it does not work. Please see the below code I have:

    Code:
    Sub FindDups ()
       
       ScreenUpdating = False
       FirstItem = ActiveCell.Value
       SecondItem = ActiveCell.Offset(1, 0).Value
       Offsetcount = 1
       Do While ActiveCell <> ""
          If FirstItem = SecondItem Then
            ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
            Offsetcount = Offsetcount + 1
            SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
          Else
            ActiveCell.Offset(Offsetcount, 0).Select
            FirstItem = ActiveCell.Value
            SecondItem = ActiveCell.Offset(1,0).Value
            Offsetcount = 1
          End If
       Loop
       ScreenUpdating = True
    End Sub
    Last edited by Excel Fox; 05-15-2013 at 08:59 AM. Reason: Code Tags Added

  2. #2
    Junior Member
    Join Date
    Jun 2012
    Posts
    8
    Rep Power
    0
    If you are try to highlight all the duplicates then you can try the code below. It is set up for Column C, to change the column change the bits in red

    Code:
    Sub DupIt()
    Dim Rng As Range
    Dim cel As Range
    Set Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
        For Each cel In Rng
            If WorksheetFunction.CountIf(Rng, cel.Value) > 1 Then
                cel.Interior.ColorIndex = 3
            End If
        Next cel
    End Sub
    Using Office 2010
    Quite often have to save as '97-
    2003 for other users

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    You can also try this:-

    Code:
    Option Explicit
    
    
     Sub lm()
     
        Dim rngRange            As Range
        Dim rngFirstRange       As Range
        Dim rngToFind           As Range
        
        Const strStringToFind   As String = "a"
        
        With ThisWorkbook.Worksheets("Sheet1")
            Set rngToFind = .Range("A1").CurrentRegion
            With rngToFind
                Set rngRange = .Find(strStringToFind, LookIn:=xlValues, lookat:=xlWhole)
                If Not rngRange Is Nothing Then
                    Set rngFirstRange = rngRange
                    Do
                        rngRange.Interior.ColorIndex = 3
                        Set rngRange = .FindNext(rngRange)
                    Loop While Not rngRange Is Nothing And rngRange.Address <> rngFirstRange.Address
                End If
            End With
        End With
        
        Set rngRange = Nothing
        Set rngFirstRange = Nothing
        Set rngToFind = Nothing
     
     End Sub

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    If you are 2007+ then why don't use conditional formatting to highlight duplicates ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    5
    Rep Power
    0
    Thanks Everyone, using excel 2003 unfortunately at work....

  6. #6
    Junior Member
    Join Date
    Jun 2012
    Posts
    8
    Rep Power
    0
    Quote Originally Posted by shonu View Post
    Thanks Everyone, using excel 2003 unfortunately at work....
    The codes in posts #2 and #3 are 2003 compatible. Did they not work for you?
    If not what are your issues with the codes?
    Using Office 2010
    Quite often have to save as '97-
    2003 for other users

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    If the data in Column C (the column I am assuming you want to check for doubles... change the highlighted C's to the actual column letter for your data) is all text, that is, no formulas, then here is one more macro for you to try (this one uses no loops and should be fast... no promises on that though)...
    Code:
    Sub MarkDuplicates()
      Dim Addr As String
      Addr = "C1:C" & Cells(Rows.Count, "C").End(xlUp).Row
      Range(Addr) = Evaluate("IF(COUNTIF(" & Addr & "," & Addr & ")>1,""=""&" & Addr & "," & Addr & ")")
      On Error Resume Next
      Range(Addr).SpecialCells(xlFormulas).Interior.ColorIndex = 6
      Range(Addr).Replace "=", "", xlPart
    End Sub
    By the way, if your data column does contain formulas, I can modify the code to account for that... just let me know.

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2013, 01:08 PM
  2. Replies: 10
    Last Post: 05-23-2013, 12:30 PM
  3. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  4. Highlight Active Cell’s Row and Column
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  5. Replies: 7
    Last Post: 04-22-2013, 01:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •