Conditional Formatting

[MarkyBoy]

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

 

[barry houdini]

There's no need to copy the conditional formatting at all if you follow my advice above.

Just select the whole range first, then you only need to apply conditional formatting once, using the condition that applies to the first cell in the range. The conditions will adjust automatically for each of the cells in the range.

E.g. if your range is A1:A100 then select that range from A1 down and apply this formula

=(COUNTIF($A$1:$A$100,A1)>2)*(A1<>"")

the second part stops blank cells being highlighted

 

[MarkyBoy]

ok cool, however that is still basing the calculation on a specific cell.

I have a range of information A1:D10

I want to format the whole range to change colour if more than 2 values are the same in the range C2:C9

its the range formatting I am still stuck with

thanks

 

[Peter_SSs]

ok cool, however that is still basing the calculation on a specific cell.

I have a range of information A1:D10

I want to format the whole range to change colour if more than 2 values are the same in the range C2:C9

its the range formatting I am still stuck with

thanks

I'm not exactly sure what you mean here, but if the result below is what you want, Select A1:D10 first and then use the CF formula shown.

Excel Workbook
	A	B	C	D
1	a		Tim	u
2	b	l	Tom	v
3			Ted	w
4	d	n	Tim	x
5	e	o	Ted
6	f	p	Joe
7	g	q	Tim
8	h	r	Tom	2
9	i	s	Ted	3
10	j	t	Sue	4
Conditional Format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =(COUNTIF($C:$C,$C1)>2)*(A1<>"")	Abc

 

[barry houdini]

I have a range of information A1:D10

I want to format the whole range to change colour if more than 2 values are the same in the range C2:C9

Do you really mean that? It seems a little different from what you originally asked. To do that

Select A1:D10

use this formula in conditional formatting

=MAX(FREQUENCY($C$2:$C$9,$C$2:$C$9))>2