Please need help with conditional formatting

Posted by Vera on October 18, 2001 11:26 AM

Please need help with conditional formatting.

In column A1:A8 I have empty cells or numbers; I need to get flagged (fill the cell with different colors for every different set of similar numbers ) whenever the number in a cell is not unique among the range A1:A8. As I know the conditional formatting is limited to 3 conditions but I will be satisfied even with limit (in my case sometimes I would need more than 3 conditions). Ok here is my example

A1 3 fill cell in green

A2 5555 no filling

A3 3 fill cell in green

A4 77 fill cell in red

A5 3 fill cell in green

A6 222 fill cell in blue

A7 77 fill cell in red

A8 222 fill cell in blue

I hope that is clear enough; thank you so much in advance

Posted by Vera on October 19, 2001 5:38 AM

Please please, I know that's challenging/hard but maybe someone has a solution; thanks

Posted by Juan Pablo on October 19, 2001 6:16 AM

Re: Please please, I know that's challenging/hard but maybe someone has a solution; thanks

Ok, you can do this using conditional formatting, but you won't get the expected results, instead you can tell excel to color cells red when a number is found twice, blue when it is three times and yellow if more than three. If this suits then, select A1:A8, and go to Format, Conditional formatting.

In the first, select formula and put
=COUNTIF($A$1:$A$8,A1)=2
and click on the pattern button, and select Red or the color you want.
In the second formula put
=COUNTIF($A$1:$A$8,A1)=3
and select Blue.
In the third formula put
=COUNTIF($A$1:$A$8,A1)>3
and select Yellow.

If you need EXACTLY your results then post again and we'll do it with VBA

Juan Pablo

Posted by Vera on October 24, 2001 8:27 AM

Re: Please please, I know that's challenging/hard but maybe someone has a solution; thanks

Thank you Juan but your solution does not work because I can have as ranks any number; only the first rank always would be 1 but after that next can be any number because as rank 1 sometimes I have let's say 3 duplicates other time 9 a.s.o.

thank you anyway