I am looking for VB code that simply highlights all duplicate cells across all sheets in a workbook. It does not matter which row, column, or sheet co

RobertN

New Member
Joined
Jan 10, 2020
Messages
19
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am looking for VB code that simply highlights all duplicate cells across all sheets in a workbook. It does not matter which row, column, or sheet contains the duplicate value.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows
So you want to compare every cell in a workbook with every other cell in a workbook? Or are we talking of a specific value? If its just generally comparing every cell with every other cell you may want to rethink your approach as you will either wait a very long time or your machine may melt.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Maybe
VBA Code:
Sub RobertN()
   Dim Cl As Range
   Dim Ws As Worksheet
  
   Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Ws In Worksheets
         For Each Cl In Ws.UsedRange
            If Cl.Value <> "" Then
               If Not .Exists(Cl.Value) Then
                  .Add Cl.Value, Cl
               Else
                  Cl.Interior.Color = 456789
                  .Item(Cl.Value).Interior.Color = 456789
               End If
            End If
         Next Cl
      Next Ws
   End With
End Sub
This may take some time if you have a large workbook.
 
Solution

RobertN

New Member
Joined
Jan 10, 2020
Messages
19
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sub RobertN() Dim Cl As Range Dim Ws As Worksheet Application.ScreenUpdating = False With CreateObject("scripting.dictionary") For Each Ws In Worksheets For Each Cl In Ws.UsedRange If Cl.Value <> "" Then If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl Else Cl.Interior.Color = 456789 .Item(Cl.Value).Interior.Color = 456789 End If End If Next Cl Next Ws End With End Sub
Maybe
VBA Code:
Sub RobertN()
   Dim Cl As Range
   Dim Ws As Worksheet

   Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Ws In Worksheets
         For Each Cl In Ws.UsedRange
            If Cl.Value <> "" Then
               If Not .Exists(Cl.Value) Then
                  .Add Cl.Value, Cl
               Else
                  Cl.Interior.Color = 456789
                  .Item(Cl.Value).Interior.Color = 456789
               End If
            End If
         Next Cl
      Next Ws
   End With
End Sub
This may take some time if you have a large workbook.

Maybe
VBA Code:
Sub RobertN()
   Dim Cl As Range
   Dim Ws As Worksheet
 
   Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Ws In Worksheets
         For Each Cl In Ws.UsedRange
            If Cl.Value <> "" Then
               If Not .Exists(Cl.Value) Then
                  .Add Cl.Value, Cl
               Else
                  Cl.Interior.Color = 456789
                  .Item(Cl.Value).Interior.Color = 456789
               End If
            End If
         Next Cl
      Next Ws
   End With
End Sub
This may take some time if you have a large workbook.
Thanks a million, Fluff! It's doing exactly what I need it to do. You're the best!!
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hey Fluff. If the first value i hit is a duplicate elsewhere you will need to run the whole workbook again wouldnt you? The first hit of a duplicated value isnt going to be recognised as such.
 

RobertN

New Member
Joined
Jan 10, 2020
Messages
19
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So you want to compare every cell in a workbook with every other cell in a workbook? Or are we talking of a specific value? If its just generally comparing every cell with every other cell you may want to rethink your approach as you will either wait a very long time or your machine may melt.
Thanks for your reply, Steve. My workbook isn't that large, so the code Fluff gave me worked for my purposes. have a terrific weekend!
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Careful when you have painted your workbook and then change it so a dup is no longer a dup.
 

RobertN

New Member
Joined
Jan 10, 2020
Messages
19
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That's what this part does .Item(Cl.Value).Interior.Color = 456789
@Fluff Sorry to bother you again. I found that after removing the duplicate and running the macro again, the original highlighting remains. Is there a way to first clear all color when the macro runs?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,033
Messages
5,639,658
Members
417,104
Latest member
Nelsini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top