Conditional Format for a number

[Livin404]

I would like to have a 10 digit number that only includes numbers. The formula that I'm currently using will flag anything over and under 10 digits. However, it will not flag anything other than a number like text or a space. Because what this document is used for I don't want to use data validation for this project. The formula I'm currently using is =AND(ISNUMBER(--C2),LEN(C2)<>10). I'm using the not equal because if this is filled out in error I need it highlighted. Also not critical but if I can have the 10 digit number lead off with a 1, it would be fantastic.

Thank you,
Sheridan

 

[Eric W]

Maybe:

=OR(LEFT(C2)<>"1",ISERROR(-C2),LEN(C2)<>10)

 

[Livin404]

That's perfect thank you!!

 

[Livin404]

I don't mean to get carried away, this particular one is a little annoying. I'm trying to avoid multiple rules in conditioning formatting if possible.
I think I would use something like =SUMPRODUCT(--ISNUMBER(SEARCH(Dairy,$A2))), but I would need to use that multiple times. Any idea how to put them all in one formula?

 

[Rick Rothstein]

I would think this more direct formula would also work...

=OR(C2<1000000000,C2>1999999999)

 

[Eric W]

Livin404, I'm a little confused as to what you want. Do you want to highlight the rows in column B where the food type in column A doesn't match the lists from sheet2? If so, why is Cheese highlighted? If not, please explain what your reasoning is for the Conditional Highlighting.


Rick, spot on! I didn't recognize offhand that would also handle non-numeric values.

 

[Livin404]

Livin404, I'm a little confused as to what you want. Do you want to highlight the rows in column B where the food type in column A doesn't match the lists from sheet2? If so, why is Cheese highlighted? If not, please explain what your reasoning is for the Conditional Highlighting.


Rick, spot on! I didn't recognize offhand that would also handle non-numeric values.

Hi yes the ones in that Column would be highlighted. Yes if they're are not equal to what is on sheet 2 they would get highlighted.

 

[Eric W]

Maybe something like this?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Food Type	Group		Food Type		Vegetables	Starches	Fruit	Dairy
2	S	Pasta		V		peas	Potato	Apple	Yogurt
3	S	Pasta		S		corn	Pasta	Pear	Cottage Cheese
4	S	Pasta		F		Broccoli	Rice	Banana	Cheese
5	S	Kale		D		Kale	Beans	Orange	Milk
6	F	Plum				Cauliflower	Cereal	Plum
7	F	Yam				yam		Peach
8	D	Cheese
9	D	Cheese
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B9	Expression	=ISERROR(MATCH(B2,CHOOSE(SEARCH(A2,"VSFD"),Vegetables,Starches,Fruit,Dairy),0))	text	NO

 

[Livin404]

That is beautiful and spot on. In the states and if I were a hunter I would say that is a 12 Point Buck, very impressive.
I have something kind of similar, but I'm sure it's a much easier formula. I have one formula that works, but it does the opposite of what I need. I have one column where I would enter a two letter country code in. It's not dependent on another cell. I'm am also using a data range called COUNTRY. I'm using =SUMPRODUCT(--ISNUMBER(SEARCH(COUNTRY,$S2))) which I can highlight if the information is correct. I'm trying to reverse it. I thought about putting an IFERROR formula but i can't figure out the syntax. I just recently trimmed around the formula you just provided to no avail.

 

[Eric W]

I'd think MATCH would work better than SUMPRODUCT(ISNUMBER(SEARCH. Try:

=ISERROR(MATCH(S2,COUNTRY,0))

If S2 isn't found in COUNTRY, it should highlight. Is the COUNTRY range just a list of the 2-digit codes? Or is it looking for "CA" in "CANADA"? I'd think that would cause a problem with "CAMBODIA" though.