Conditional Formatting

MarkyBoy

New Member
Joined
Aug 2, 2007
Messages
47
Hi there, this is my first post so sorry if its been asked before but I couldnt find anything anywhere else.

I am trying to conditionally format cells if any value in a range of cells appears more than twice.

so I have a list of values in a column and I want to highlight any where the values is listed 3 or more times

I have tried using countif but have been unsuccessful.

Please help

Thanks in advance
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
For the range A1:A20 use the following conditional format formula on A1 and then copy&paste formats to the rest of the range:
Code:
=COUNTIF($A$1:$A$20,A1)>2
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Assuming your column is A, select that column and use Coditional formatting with "formula is" option

=COUNTIF(A:A,A1)>2

choose required format > OK
 

MarkyBoy

New Member
Joined
Aug 2, 2007
Messages
47
thanks for the swift responses, however I think I have missed a major point, the values in question are words not numbers so maybe saying value was a bad choice of words.

this is why I am having the problem

I want to highlight the cells if any word is repeated in a column more than twice

sorry for confusion
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,412
Office Version
  1. 365
Platform
  1. Windows
Same solution:

Excel Workbook
A
1Tim
2Tom
3Ted
4Tim
5Ted
6Joe
7Tim
8Tom
9Ted
10
Conditional Format
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =COUNTIF(A:A,A1)>2Abc
A21. / Formula is =COUNTIF(A:A,A2)>2Abc
A31. / Formula is =COUNTIF(A:A,A3)>2Abc
A41. / Formula is =COUNTIF(A:A,A4)>2Abc
A51. / Formula is =COUNTIF(A:A,A5)>2Abc
A61. / Formula is =COUNTIF(A:A,A6)>2Abc
A71. / Formula is =COUNTIF(A:A,A7)>2Abc
A81. / Formula is =COUNTIF(A:A,A8)>2Abc
A91. / Formula is =COUNTIF(A:A,A9)>2Abc
A101. / Formula is =COUNTIF(A:A,A10)>2Abc
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Doesn’t matter, it will still do the same thing whether it’s numbers or text.
 

MarkyBoy

New Member
Joined
Aug 2, 2007
Messages
47
Many thanks, I now see that working although I need to do this for every cell individually, is there no way to format the whole range in one go?

also another little niggle following this, the words that it is lokking up are a result from a formula in the cell, normally this cell shows as blank as the query is returning no result, however this means that if the field in question has no entry then it looks up the column and sees other cells with no result and formats the cells which I do not want, do I want it to ignore blank cells or do I need to make it ignore cells with no current results?

sorry for such nuisance
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Copy the formatted cell and paste special>formats to the rest of the range.
 

MarkyBoy

New Member
Joined
Aug 2, 2007
Messages
47
Lewiy, that's great, any idea on how to stop if formatting when the cells are empty?
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,786
Latest member
Deniel

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