Highlight Duplicates (existing macro)

[sherwinarae]

Hi All,

I have an existing macro which performs a sumifs function in a current selection (this selection changes every month which is why i cannot hardcode a range).
It then copies / pastes values.
It then goes to the next tab and does the same thing.

I need to add code which highlights duplicate values in that original selection (as long as they are not equal to 0), and then continue to move onto the rest of the macro when finished.

Many thanks in advance!


Here is the macro:

'PASTE MONTHLY ACTUALS JO


Sheets("JO OTHER").Select
Range("AI147").Select
Selection.End(xlToRight).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(535, 1)).Select
Selection.Formula = "=SUMIFS(OEENEW!$R:$R,OEENEW!$X:$X,""JUDGES"",OEENEW!$A:$A,$C147)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


[I would think this is where the duplicate code would go as the cells to be evaluated are already selected?]

Application.CutCopyMode = False


'PASTE MONTHLY ACTUALS - EXEC


Sheets("EXEC").Select
Range("AI147").Select
Selection.End(xlToRight).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(535, 1)).Select
Selection.Formula = "=SUMIFS(OEENEW!$R:$R,OEENEW!$X:$X,""EXECUTIVE"",OEENEW!$A:$A,$C147)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[sherwinarae]

I tried this but it doesn't work

Dim MyRange As Range
Set MyRange = Selection


For Each cell In MyRange
If cell.Value <> 0 And WorksheetFunction.CountIf(MyRange, cell.Value) > 1 Then
cell.Interior.ColorIndex = 6
End If

Next cell

 

[sherwinarae]

This is it!!! i just need to eliminate 0's from the equation, how do i do that???

With Selection
.FormatConditions.Delete
.FormatConditions.AddUniqueValues
.FormatConditions(1).DupeUnique = xlDuplicate
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With