VB Conditional formatting for three matches or no matches

[sachavez]

Excel 2007

I am using the following code to find duplicates and change the font to red. The code works well.

    NUMROWS = ActiveSheet.UsedRange.Rows.Count
    Range("E2:E" & NUMROWS).Select
    Set uv = Selection.FormatConditions.AddUniqueValues
    uv.DupeUnique = xlDuplicate
    uv.Font.Color = vbRed

Next, I would like to change the cell color to vbYellow (cell color in column E) if there are three of the same number in column I.

And then, change the cell color to vbGreen if there are no matches.

Any advice?

Thanks, in advance.

 

[CodeNinja]

Sub test()

NUMROWS = ActiveSheet.UsedRange.Rows.Count
Range("E2:E" & NUMROWS).Select
Set uv = Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
uv.DupeUnique = xlDuplicate
uv.Font.Color = vbRed

Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(COUNTIF(E:E, ""=""&E2)>=3,E2<>"""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Interior.Color = vbYellow

Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($E$2:$E$10, E2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Interior.Color = vbGreen
Selection.FormatConditions(1).StopIfTrue = False

End Sub

 

[sachavez]

Code Ninja,

I apprecaite your blackbelt skills! This code looks promising. I will run on Saturday AM and give feedback!

Thanks a million!

Steve