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

==============================

​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 = xlDuplicateWith .FormatConditions(1).Font.Color = -16777024.TintAndShade = 0End WithWith .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic.Color = 10284031.TintAndShade = 0End WithSelection.FormatConditions(1).StopIfTrue = False’Check if Dups are thereFor Each rngCell In .CellsStopIf rngCell.FormatConditions(1).Interior.Color = 10284031 And rngCell.FormatConditions(1).Font.Color = -16777024 ThenrngCell.SelectMsgBox “Dups are still there”StopEnd IfNext rngCellEnd WithEnd Sub==============================  Read More

​