Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

Write an excel macro that will highlight duplicated cells

  1. User

    Write an excel macro that will highlight duplicated cells

  2. Assistant

    Sure! Here is an example of an Excel VBA macro that will highlight duplicated cells in a specific range:

    Sub HighlightDuplicates()
        Dim rng As Range
        Dim cell As Range
        
        ' Set the range where you want to highlight duplicates
        Set rng = Range("A1:A10") ' Change the range as per your requirement
        
        ' Clear any existing cell colors
        rng.Interior.ColorIndex = xlNone
        
        ' Loop through each cell in the range
        For Each cell In rng
            ' Check if the cell value is duplicated in the range
            If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then
                ' Highlight the cell with a color
                cell.Interior.Color = RGB(255, 0, 0) ' Change the color as per your requirement
            End If
        Next cell
        
        ' Optional: Display a message box when the macro finishes
        MsgBox "Duplicates highlighted!"
        
    End Sub
    

    To use this macro in your workbook, follow these steps:

    1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
    2. Insert a new module by clicking on Insert -> Module.
    3. Paste the code into the module window that appears.
    4. Change the Set rng = Range("A1:A10") line to specify the range where you want to highlight duplicates.
    5. Press F5 to run the macro, or you can assign the macro to a button or a keyboard shortcut for easy access.

    When the macro is executed, it will highlight any duplicated cells within the specified range with the color specified in the code (currently set to red). You can modify the code to customize the range and color as per your requirements.

  3. User

    Rewrite that formula, there's a syntax error

  4. Assistant

    Apologies for the syntax error in the previous response. Here is the revised code:

    Sub HighlightDuplicates()
        Dim rng As Range
        Dim cell As Range
    
        ' Set the range where you want to highlight duplicates
        Set rng = Range("A1:A10") ' Change the range as per your requirement
    
        ' Clear any existing cell colors
        rng.Interior.ColorIndex = xlColorIndexNone
    
        ' Loop through each cell in the range
        For Each cell In rng
            ' Check if the cell value is duplicated in the range
            If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then
                ' Highlight the cell with a color
                cell.Interior.Color = RGB(255, 0, 0) ' Change the color as per your requirement
            End If
        Next cell
    
        ' Optional: Display a message box when the macro finishes
        MsgBox "Duplicates highlighted!"
    
    End Sub
    

    Please replace the previous code with this revised version and try executing the macro again.