Conditional Formatting Wont Work

[jaxisdex]

Hi guys, all I want is to set up a format where if the value has a triplicate or more it would highlight. Seems simple enough and this was my formula, =countif($K$5:$K$30,$K5)>=$M$3 where M3 is 3. Or i tried =countif($K$5:$K$30,$K5)>=3 but neither seems to work correctly. Could someone let me know what I am doing wrong please? It seems like highlight random cells where there is only 1 value or 2 at times.

Ps, i thought maybe it was my formula settings might not be on automatic. they are in fact set to automatic.

Thank you for the assistance!

 

[Joe4]

Seems to work fine for me.
Are you sure that you have all your range references correct?
Are you highlighting the range K5:K30, and then entering the formula you listed above in?

One thing you can do to test is to pick an available column, and in row 5 enter your formula:
=countif($K$5:$K$30,$K5)
and copy down to row 30 and see what it is returning for each count.
Is it returning the correct/expected values?

 

[jaxisdex]

I just highlight the entire column and the formula says it is from $K:$K. I've tried testing it with different values but nothing... doesnt seem to work correctly. It works fine whenever i use the default find duplicates, but that isnt what i want haha.

I also tried to record a macro to see if there was something different or wrong compared to the find duplicates function. Other than my countif function it is the exact same even when playing around with the range values.

 

[Joe4]

I just highlight the entire column and the formula says it is from $K:$K. I've tried testing it with different values but nothing... doesnt seem to work correctly

Don't do that! You will be off by 4 rows, since you are starting your formula with row 5 (K5). You want to make sure that you ONLY highlight the rows/columns you want to apply it to, and you write your formula as in pertains to the FIRST row/column you have highlighted/selected.

So if you highlight the range K5:K30 and enter the formula as you have written it, it should work.

 

[jaxisdex]

Oh wow, that worked perfectly! Hmm.... I would have one last question about this... Would i need to highlight it everytime before applying the formula? i ask this because currently i have a macro recorded where it goes from K:K and colors duplicates. Is there a way to get it set to highlight just the values then? Should i just move up all the values to row 1 so that way i can have it set to K:K? Or should i write in the code to go from $k$5 all the way down to like $k$1000?

But either way thank you so much for your help! i cant believe how simple of a fix that way... You are awesome!

 

[Joe4]

Well, you could do it by highlighting the whole column, you will just need to change your column range to start at K1, not K5 (since your first cell is in row 1), i.e.
=countif($K$1:$K$30,$K1)