A1 = data validation list with names A to Z ( range is NAMES )
How can conditionally format these names that when one is chosen the colors
will be accordinly to the letter that the name start with :
ex,. A to H names will come color blue.
I to O names will come color red.
and finally names starting with P to Z will come in color green.
THANKS for helping.

One way...

Select cell A1
Goto the menu FormatConditional Formatting
Condition 1 (A - H)
Select the Formula is option
Enter this formula in the little box on the right:

=AND(UPPER(LEFT(A1))=CHAR(65),UPPER(LEFT(A1))<=CH AR(72))

Click the Format button
Select the style(s) desiredOK

Click the Add buton

Repeat the above steps and use these formulas for conditions 2 and 3:

Condition 2 (I - O)
=AND(UPPER(LEFT(A1))=CHAR(73),UPPER(LEFT(A1))<=CH AR(79))

Condition 3 (P - Z)
=AND(UPPER(LEFT(A1))=CHAR(80),UPPER(LEFT(A1))<=CH AR(90))

OK out

--
Biff
Microsoft Excel MVP


"computexcel" wrote in message
...
A1 = data validation list with names A to Z ( range is NAMES )
How can conditionally format these names that when one is chosen the
colors
will be accordinly to the letter that the name start with :
ex,. A to H names will come color blue.
I to O names will come color red.
and finally names starting with P to Z will come in color green.
THANKS for helping.

Improvement.

The formulas can be shortened to:

=AND(UPPER(LEFT(A1))="A",UPPER(LEFT(A1))<="H")
=AND(UPPER(LEFT(A1))="I",UPPER(LEFT(A1))<="O")
=AND(UPPER(LEFT(A1))="P",UPPER(LEFT(A1))<="Z")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

Select cell A1
Goto the menu FormatConditional Formatting
Condition 1 (A - H)
Select the Formula is option
Enter this formula in the little box on the right:

=AND(UPPER(LEFT(A1))=CHAR(65),UPPER(LEFT(A1))<=CH AR(72))

Click the Format button
Select the style(s) desiredOK

Click the Add buton

Repeat the above steps and use these formulas for conditions 2 and 3:

Condition 2 (I - O)
=AND(UPPER(LEFT(A1))=CHAR(73),UPPER(LEFT(A1))<=CH AR(79))

Condition 3 (P - Z)
=AND(UPPER(LEFT(A1))=CHAR(80),UPPER(LEFT(A1))<=CH AR(90))

OK out

--
Biff
Microsoft Excel MVP


"computexcel" wrote in message
...
A1 = data validation list with names A to Z ( range is NAMES )
How can conditionally format these names that when one is chosen the
colors
will be accordinly to the letter that the name start with :
ex,. A to H names will come color blue.
I to O names will come color red.
and finally names starting with P to Z will come in color green.
THANKS for helping.

Hi

Setup 3 conditions in CF, each tume "Formula is"

Condition 1:

=AND(CODE(A1)=65,CODE(A1)<=72)

Give it a blue color.

Condition 2:

=AND(CODE(A1)=73,CODE(A1)<=79)

Give it a red color.

Condition 3:

=AND(CODE(A1)=80,CODE(A1)<=90)

Give it a green color.


Alternatively, format A1 in a blue color by default, and exclude the first
condition.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"computexcel" wrote:

A1 = data validation list with names A to Z ( range is NAMES )
How can conditionally format these names that when one is chosen the colors
will be accordinly to the letter that the name start with :
ex,. A to H names will come color blue.
I to O names will come color red.
and finally names starting with P to Z will come in color green.
THANKS for helping.