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]

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.

 

[steve the fish]

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]

Maybe

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.

 

[RobertN]

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

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

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]

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]

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]

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

 

[Fluff]

The first hit of a duplicated value isnt going to be recognised as such.

That's what this part does .Item(Cl.Value).Interior.Color = 456789

 

[Fluff]

Thanks a million, Fluff! It's doing exactly what I need it to do. You're the best!!

Glad we could help & thanks for the feedback.

 

[RobertN]

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?