gpt-3.5-turbo-0613· OpenAI
WildChat-4.8M (AI2)
Write an excel macro that will highlight duplicated cells
- User
Write an excel macro that will highlight duplicated cells
- 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 SubTo use this macro in your workbook, follow these steps:
- Press
Alt + F11to open the Visual Basic for Applications (VBA) editor. - Insert a new module by clicking on
Insert -> Module. - Paste the code into the module window that appears.
- Change the
Set rng = Range("A1:A10")line to specify the range where you want to highlight duplicates. - Press
F5to 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.
- Press
- User
Rewrite that formula, there's a syntax error
- 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 SubPlease replace the previous code with this revised version and try executing the macro again.