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
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
MaybeThis may take some time if you have a large workbook.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
Thanks a million, Fluff! It's doing exactly what I need it to do. You're the best!!MaybeThis may take some time if you have a large workbook.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
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!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 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?That's what this part does.Item(Cl.Value).Interior.Color = 456789