Conditional Formatting for 2,3,4,5 of the same numbers.

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,264
Office Version
  1. 2007
Platform
  1. Windows
Hi everyone,

I need help with a CF that would format when 2 numbers are the same or 3 the same or 4 or5.

A1:E1

43,43,48,52,55. the 2, 43 would be highlighted with 1 color.
or
43,43,43,52,55. the 3, 43 would be highlighted a different color, etc...

and if I have :

43,43,43,55,55. the 3, 43 and 2, 55 would be highlighted a different color each.

I hope it's clear enough ?

Thank you for help... Serge.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Select the range A1:E1 and use this formula in conditional formatting

=COUNTIF($A1:$E1,A1)=2

format as required and repeat for 3, 4 and 5

(I assume you have Excel 2007 or later otherwise you are limited to 3 formats.....)
 

serge

Well-known Member
Joined
Oct 8, 2008
Messages
1,264
Office Version
  1. 2007
Platform
  1. Windows
Hi Barry,

Thank you very much, that's exactly what I needed.

Regards... Serge.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
Barry, Great solution..

On a related note..
I've noticed (in xl2007) if you were to now Click on any cell A1:E1 (after applying the c/f) and you use the Format Painter to c/f the Range(B1:E4) - When you then click on say Cell C3, and then C/f Manage Rules - both the formula box and the Applys to box
show "strange" cell references. Am I seeing correctly?

Jim
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,105
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top