Hi Amy,

Someone else may know a better way to do this. But here is the
long-winded way!

Lets say cell A1 contains your text string (ABCDE)

In cell B1 use =LEFT(A1,1)
In cell C1 use =MID(A1,2,1)
In cell D1 use =MID(A1,3,1)
In cell E1 use =MID(A1,4,1)
In cell F1 use =MID(A1,5,1)

To split the concantenated string into individual cells.

Then

In E1 =COUNTIF(B1:F1,"A")
In F1 =COUNTIF(B1:F1,"B")
In G1 =COUNTIF(B1:F1,"C")
In H1 =COUNTIF(B1:F1,"D")
In I1=COUNTIF(B1:F1,"E")

This will give you a raw count of how many A's, B's, C's, D's and E's.

Last but not least....the final cell....

=IF(E1+F13,"green",IF(G13,"amber,IF(H1+I13,"red ",0)))

Of course this will only return the colour as a text string inside your
calculation cell. Simply replace 'green', 'amber' and 'red' with
whatever values you want, then use conditional formatting to colour
appropriately.

There is probably a much smarter way to do this.....hope someone finds
it for you :)

Regards

Rob


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=539852