Forum Discussion
How to find Duplicates in a Range
Hello All Excelers,
Oh Boy why is this not working???
The code below is supposed to identify cells that are duplicates in a range of cells.
I apply the Conditional Formatting (CFs) to let Excel find the duplicates and then I test to see if the Font Color and Interior color are the ones assigned by the CFs and if they are then let me know.
rngCellsToCombine is a range of cells that need to be combines into another range to go to a MS-Access Database table. rngCellsToCombine has a header so I resize it to eliminate the header row.
Then I apply the CFs to color the duplicates.
Then I loop cell by cell to find the duplicates based on the Font and Interior colors.
BUT THIS DOES NOT WORK! The message is always coming up regardless if the cell is a duplicate or not!!! WHY??? Can you see something I am missing?
==============================
Sub NotWorking()
With rngCellsToCombine.Offset(-1, 0).Resize(rngCellsToCombine.Rows.Count + 1, 1)
'Apply CFs.
.FormatConditions.AddUniqueValues
.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).DupeUnique = xlDuplicate
With .FormatConditions(1).Font
.Color = -16777024
.TintAndShade = 0
End With
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'Check if Dups are there
For Each rngCell In .Cells
Stop
If rngCell.FormatConditions(1).Interior.Color = 10284031 And rngCell.FormatConditions(1).Font.Color = -16777024 Then
rngCell.Select
MsgBox "Dups are still there"
Stop
End If
Next rngCell
End With
End Sub
==============================
3 Replies
GeorgieAnne In your For Each loop, try replacing FormatConditions(1) with DisplayFormat in two places. Also, color -16777024 is equivalent to 192, so you may need to use 192 when reading back the font color:
If rngCell.DisplayFormat.Interior.Color = 10284031 And rngCell.DisplayFormat.Font.Color = 192 Then...
On a side note, it's not very clear how you've defined rngCellsToCombine, and the Offset.Resize method is doing the opposite of what you've described (it's adding a header instead of removing one). Also, if you run this code over and over again, it will create a new Conditional Formatting rule each time, so you'll end up with multiple duplicate CF rules. You may want include a bit of code at the start to check for and delete existing rules first.
- Thank You djclements,
I will make the suggested changes and let you know the out come.
The rngCellsToCombine is a range on a temporary worksheet that use to manipulate the data before it is combined to other ranges (as values with no formatting) and then the Access Database. So, yes the CFs will only be temporary for the duration of Data cleanup.
This is an output of manufacturing machines, and each output has 5 sets of values: A) The machine task code, a 7-digit unique number. B) The time the machine started the task, C) If the machine paused for some reason then the time of the start of the pause is captured, D) the time the machine went back to the task and resumed the task (This could be milliseconds and in Excel we represent this value with a "?", or several hours sometimes and that is what we are capturing (in seconds), and E) the time the task ended successfully or did not end successfully, then a code is given of the issue with the machine, and the engineers will look at the code and fix (adjust) what needs to be fixed.. The engineers monitor the Access Database for these codes.
To complicate things, there are 26 machines, all have the same firmware and produce the same task codes and timings and error codes, so the cleaning up is to make sure Task A from machine 1 is segregated from Task A from machine 2! You do wat to fix the machine that is broken!
Thanks for your help, I will reply and let you know how it worked.
GiGi.
