DV + CF

[computexcel]

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.

 

[T. Valko]

One way...

Select cell A1
Goto the menu Format>Conditional 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))<=CHAR(72))

Click the Format button
Select the style(s) desired>OK

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))<=CHAR(79))

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

OK out

 

[T. Valko]

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")

 

[computexcel]

Yes! Thanks.

 

[Wigi]

Hmmm, just rethinking my solution...

This is easier:

Format A1 in a green color by default.

Setup 2 conditions in CF:

Condition 1:

"Cell value is less than or equal to"
="H"

Give it a blue color.

Condition 2:

"Cell value is less than or equal to"
="O"

Give it a red color.


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


:

 

[Wigi]

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.