Question 2:Color cell using conditional formatting

[Gertjan]

Hi,

Using conditional formatting to change color of cell if
something is entered works! (thanks Anonnymous!)

Now, how woudl I go about to change the color to red if
data entered starts with a 1, and change to yellow if if
it starts with a letter. I have seen that you can add a
2nd condition in conditional formatting, but I cannot find
what formula to use. Or should I use cell value is?

Thanks again!!

 

[Jason Morin]

For the 1st condition, use:

=LEFT(A1)*1=1

For the 2nd:

=OR(AND(CODE(LEFT(A1))>=65,CODE(LEFT(A1))<=90),AND(CODE
(LEFT(A1))>=97,CODE(LEFT(A1))<=122))

HTH
Jason
Atlanta, GA

 

[EarlK]

Gertjan,

For text, use the "Formula is" option with:

=ISTEXT(A1)

Change the A1 to whatever cell is active (white) in your selection. There
should be no absolute references (dollar signs). Don't forget to click the
"Format" button and select the formatting you want to see for this
condition.

For a second condition (stuff that starts with 1), click Add, then use
"Formula is" with:

=LEFT(A1,1)="1"

Then click the "Format" button and select the formatting for this condition.

Since these two conditions are mutually exclusive, it doesn't matter which
condition is the first one.

 

[Gertjan]

Dead right again! Damn you are good. Thanks!

 

[Ron Rosenfeld]

Since these two conditions are mutually exclusive, it doesn't matter which
condition is the first one.

Note that they are not mutually exclusive.

A1: 1abc

=ISTEXT(A1) = true

and even

=ISTEXT(LEFT(A1,1)) = true


--ron