MsgBox stuck in loop when searching for cell color [For Each / If Then] loop

SheriffWoody

New Member
Joined
Jul 7, 2015
Messages
3
I am aiming to select a column and highlight any duplicate values as green.

I would then like to instruct Excel to do 1 of 2 things in an If Then structure for the given range:

1) filter for any potential green cells and then end sub with "Complete" msgbox

OR

2) if no cells were highlighted green as a result of the first part of the code then display "No duplicate values found" and end sub with "Complete" msgbox

Currently, the below code highlights duplicates green (no issues there) as well as filtering when duplicates are present. I'm having difficulty constructing the If_Then ... ElseIf statement so that when NO duplicates are found, a message appears stating "No Duplicates Found" and then closing the sub.

Any help would be greatly appreciated! I just starting self teaching VBA on Thursday so go easy :)

- SheriffWoody

CODE:

'Identify Dups with green highlight

Dim DupCell As Variant
Dim DupRange As Range

Range("C5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
Range(Selection, Selection.End(xlUp)).Select
Set DupRange = Selection

DupRange.Interior.ColorIndex = xlNone

For Each DupCell In DupRange
If Application.WorksheetFunction.CountIf(DupRange, DupCell) > 1 Then
DupCell.Interior.ColorIndex = 4

End If
Next

'Filter for green dups

Application.ScreenUpdating = True

For Each DupCell In DupRange

If DupCell.Interior.ColorIndex = 4 Then
DupRange.AutoFilter Field:=6, Criteria1:=RGB(0, 255, 0), Operator:=xlFilterCellColor

ElseIf DupCell.Interior.ColorIndex = 0 Then
MsgBox "No duplicates found!", vbInformation, "* * * C O M P L E T E * * *"

End If

Next

MsgBox "Duplicate Check Complete"
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top