Forum Discussion
How to find Duplicates in a Range
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.
