Excel =LEN formula help

[ilovewilly]

I have a cell that I need to change colour when a certain amount of characters are entered.

Basically its so when a certain amount of staff initials are entered, it can change colour red,amber,green when there are enough staff to operate.

2 or 3 staff, red

4 or 5 staff, amber

6 and above, green.

as its filled out as initials,

2 staffs initials i.ee AA BB would be 5 characters, as there is a space between.

4 staff would be 11 characters.

Now,originally I used conditional formatting so that I could have it red if less than X, and green if above
=LEN<=8 which was red
=LEN>=9 which was green

which was fine, but now as I am adding an extra condition, I am stuggling.

Basically I want my formula to say: if its more than X, but less than X then it will change colour.

How would I write a LEN formula that is specific between values.

Help would be appreciated as I am confusing myself here.

 

[FlameR]

You can use 3 conditions for conditional formatting though?

Just put cell value is 'not between' the two values you're talking about

 

[ilovewilly]

Yeah can use a maximum of 3 using the tool.

But for Length of value it has to be in the 'formula' box as if I put say between 1 and 10, it would only work if those numbers where entered. As its character length of say 1 character of more, it needs a formula