How do I highlight values that appear more than 3 times?

[156Nathan]

So I've been trying to figure this out all day, I am aware of how to highlight duplicate values that exist using the conditional formatting feature, however, I only want to highlight values that exist MORE THEN 3 times. I've searched the forums and have found solutions for searching for specific values i.e. "Apple" BUT I am not looking for a SPECIFIC value, just those that exist more then 3 times, I'm dealing with an extensive amount of data i.e. 900+ pieces so I can't search each 'customer number' individually. The pic below is a small sample from my spreadsheet, if anyone could help me out that would be great, thanks. (I just want all the numbers that appear more than 3 times to be highlighted in red)

[/IMG]

 

[shg]

Welcome to the board.

Use the conditional formatting rule =COUNTIF($A$2:$D$18, A2) > 3

Row\Col	A​	B​	C​	D​
1​	Jan​	Feb​	Mar​	Apr​
2​	15​	50​	8​	34​
3​	13​	47​	8​	14​
4​	50​	40​	32​	48​
5​	33​	18​	15​	25​
6​	1​	19​	30​	47​
7​	41​	26​	26​	22​
8​	50​	39​	21​	34​
9​	43​	30​	34​	25​
10​	25​	48​	10​	41​
11​	6​	43​	15​	34​
12​	50​	7​	33​	14​
13​	28​	42​	35​	42​
14​	14​	43​	28​	42​
15​	24​	46​	39​	14​
16​	47​	26​	18​	28​
17​	13​	2​	23​	33​
18​	14​	10​	8​	36​

 

[MickG]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG09Nov30
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = ActiveSheet.Range("A1").CurrentRegion
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
        [COLOR=Navy]If[/COLOR] .Item(Dn.Value).Count > 3 [COLOR=Navy]Then[/COLOR] .Item(Dn.Value).Interior.Color = vbRed
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[156Nathan]

Thank you for the welcome, and your reply!

I tried your solution, "Conditional Formatting" --> "New Rule" --> "Use a formula to determine which cells to format" --> "=COUNTIF($A$2:$D$18, A2) > 3" --> Select conditional format i.e. yellow text" --> "OK"

However, still nothing in the table changes and obviously the number "156" does appear 4 times.

Any idea where I am going wrong? Thanks.

 

[156Nathan]

Welcome to the board.

Use the conditional formatting rule =COUNTIF($A$2:$D$18, A2) > 3

Row\Col	A​	B​	C​	D​
1​	Jan​	Feb​	Mar​	Apr​
2​	15​	50​	8​	34​
3​	13​	47​	8​	14​
4​	50​	40​	32​	48​
5​	33​	18​	15​	25​
6​	1​	19​	30​	47​
7​	41​	26​	26​	22​
8​	50​	39​	21​	34​
9​	43​	30​	34​	25​
10​	25​	48​	10​	41​
11​	6​	43​	15​	34​
12​	50​	7​	33​	14​
13​	28​	42​	35​	42​
14​	14​	43​	28​	42​
15​	24​	46​	39​	14​
16​	47​	26​	18​	28​
17​	13​	2​	23​	33​
18​	14​	10​	8​	36​

<tbody>
</tbody>

.
I literally can't figure out what's going wrong, would this feature work on Excel starter edition? Thanks

 

[blbat]

156Nathan-

go back into the rule and select "Edit Rule", check to see if any extra quotation marks snuck their way in...sometimes happens when you're copying a formula Excel will put Quotes in AFTER the equals sign.