Consulting

Results 1 to 3 of 3

Thread: Need help with highlighting duplicates of data in 3 columns, Please

  1. #1
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location

    Need help with highlighting duplicates of data in 3 columns, Please

    Sub DupFinder()Dim R As Range, t As Range
    Set t = Range("C5:G16")
    For Each R In t
    v = R.Value
    If Application.WorksheetFunction.CountIf(t, v) > 1 Then
    R.Interior.ColorIndex = 3
    End If
    Next
    End Sub
    Im working with this code and trying to get the data to be highlighted in red when ever it finds a duplicate in any of the 3 columns.
    I'm having trouble getting it to work with the data I have , its Deliminated meaning the data in each cell looks like this > 1-5-7-7-22.

    I provided file and had done the highlighting of the duplicates in red manually.
    Any help on this is appreciated!
    Sincerely,
    Dennis
    Attached Files Attached Files
    Last edited by Aussiebear; 08-07-2018 at 04:23 PM. Reason: Corrected for Proper Case

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    THERE'S REALLY NO NEED TO SHOUT IN THE POST TITLE

    Try something like this


    Option Explicit
    
    Sub DupFinder()
        Dim R As Range, t As Range
    
        Set t = Range("C5:G16")
        
        For Each R In t.SpecialCells(xlCellTypeConstants).Cells
            If Application.WorksheetFunction.CountIf(t, R.Value) > 1 Then
                R.Interior.ColorIndex = 3
            End If
        Next
    End Sub
    
    
    Last edited by Paul_Hossler; 08-07-2018 at 09:25 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Mentor
    Joined
    Feb 2016
    Posts
    382
    Location
    Thanks you very much!! it works great!!! I will remeber not to put in all caps. thanks Again!!!

Posting Permissions

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