Conditional Formatting Wont Work

jaxisdex

New Member
Joined
May 26, 2015
Messages
35
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!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 26, 2015
Messages
35
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 26, 2015
Messages
35
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,948
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,828
Members
414,260
Latest member
joishe

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
Top